HLOOKUP (Value,Array,Column,ApproximateMatchFlag)
where,
- Value is the value to be matched from an array or top row of the table,
- Array refers to two or more rows of data values,
- Column is the row number in the table from which the matching value should be returned, and
- ApproximateMatchFlag is an optional logical value that specifies whether to find an exact match or approximate match of the mentioned lookup value.
- HLOOKUP(), looks in the top row of an array and returns the value of the indicated cell.
Description
HLOOKUP (Value,Array,Column,ApproximateMatchFlag)
- The 'H' in HLOOKUP stands for Horizontal. This function looks the value row wise.
- Arguments containing text values entered as a part of command, should be enclosed in quotes (e.g. "JOHN").
- HLOOKUP function is not case-sensitive.
- If Value is less than 'smallest value in top row of table', Calci displays #NULL error message.
- If Column is 1, Calci returns the value from first row in table array. If Column is 2, Calci returns the value from second row in table array and so on.
- For Column < 1 or Column > 'number of rows in array', Calci returns #NULL error message.
- The values in first row should be arranged in ascending order for correct results.
- Argument ApproximateMatchFlag can be TRUE or FALSE.
- If ApproximateMatchFlag is TRUE or Omitted, Calci returns exact or approximate match of the value.
- Approximate value is the largest value that is <= lkup_val.
- If ApproximateMatchFlag is FALSE, Calci searches only for the exact match. If exact match is not found, Calci returns #NULL error message.
- For invalid arguments, Calci displays #NULL error message.
Examples
Following table shows the use of HLOOKUP function -
| Apple | Banana | Mango | Orange | Strawberry
|
| Red | Yellow | Yellow | Orange | Red |
| 20 | 14 | 20 | 40 | 35 |
=HLOOKUP("Banana",A1:E3,2) : Looks up for 'Banana' in first row and returns value
from second row in the same column. Returns Yellow as a result.
=HLOOKUP(C1,A1:E3,3,FALSE) : Looks up for exact match for 'Mango' in first row and
returns value from third row in the same column. Returns 20 as a result.
=HLOOKUP("P",A1:E3,3,TRUE) : Looks up for an approximate match for 'P' in first row,
matches the nearest value 'Orange' and returns value from third row in same column. Returns 40 as a result.
Related Videos
See Also
References