Syntax:
HLOOKUP(Crit, Range, n [, Sorted])
Description:
Scans in the first row of range for the first occurrence of Crit. If found, the function returns the content of the cell located in the same column and n-th row of Range.
Note: HLOOKUP can search horizontally only. To search vertically, use VLOOKUP instead.
Crit is the value to be searched for. The search is case insensitive.
Range is the cell range or array to be evaluated. Its first row should contain the values to be scanned. These can be text strings, numbers or logical values.
n is the relative row number in Range from which a value will be returned.
If n is less than 1, a #VALUE! error value is returned. If n is greater than the total number of rows in Range, a #REF! error value is returned.
Sorted is a logical value determining whether the values in the first column of Range are in sorted order:
TRUE or omitted: Values have to be sorted in ascending order.
FALSE: Values do not have to be sorted.
Important: If you do not explicitly set Sorted to FALSE, these values must be in sorted order!
Note:
If HLOOKUP does not find a value that exactly matches the Crit, the next lower value is automatically taken.
If no smaller value exists because Crit is smaller than the smallest value available, the function returns the error value #N/A.
Example:
You sell flowers at different unit prices – depending on how many flowers the customer buys. To do this, you have created a table with a discount scale:
Now you can use the HLOOKUP function to determine the price for a specific type of flower depending on the number of pieces.
Use the following arguments:
For Crit, enter the name of the flower type (i.e. "roses", "carnations" or "tulips").
For Range you enter the cell range, which contains the names of the flower types in the first row and the prices below – here B1:D4.
For n you enter the number of pieces plus one, because the first row (n=1) does not contain prices, but the names of the flowers.
Some examples:
HLOOKUP("Roses", B1:D4, 2, FALSE) returns the price for one rose, i.e. € 5.50.
HLOOKUP("Roses", B1:D4, 4, FALSE) returns the price for three roses, i.e. € 4.95.
HLOOKUP("Carnations", B1:D4, 4, FALSE) returns the price for three carnations, i.e. € 4,50.
HLOOKUP("Carnations", B1:D4, 5, FALSE) returns the error value #REF because n is greater than the row number of Range.
HLOOKUP("Petunias", B1:D4, 4, FALSE) returns the error value #N/A, because "Petunias" does not appear in the first row of Range.
See also: