Syntax:
HLOOKUP(Crit, Range, n [, Sorted])
Description:
This function searches 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 only search horizontally. To search vertically, use VLOOKUP instead.
Crit is the value for which the search is performed. 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 Crit is found.
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 that you use to specify whether the values in the first row of Range are sorted:
If Sorted is TRUE or omitted, values in the first column of Range have to be sorted in ascending order.
If Sorted is FALSE, the order of the values in the first column is irrelevant.
Important: If you do not explicitly set Sorted to FALSE, these values must be in sorted order!
Note:
If Sorted is set to TRUE and VLOOKUP does not find a value that exactly matches Crit, the next smaller value is automatically taken.
If no smaller value exists because Crit is smaller than the smallest value available, the function returns the #N/A error value.
Example:
You sell flowers at different unit prices – depending on the amount of flowers the customer buys. To do so, you have created a table with a discount scale:
You can now 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 that contains the names of the flower types in the first row and the prices below – B1:D4 here.
For n, you enter the number of pieces plus one, as the first row (n=1) contains the names of the flowers rather than the prices.
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 #REF error value because n is greater than the row number of Range.
HLOOKUP("Petunias", B1:D4, 4, FALSE) returns the #N/A error value because "Petunias" does not appear in the first row of Range.
See also: