Syntax: | HLOOKUP(X, T, N[, R]) |
XLHLOOKUP(X, T, N[, R]) |
X = a numeric or string value
T = a range containing the lookup table
N = a numeric value specifying the offset into the table
R = an optional boolean value (1 or 0) for ``range lookup''
HLOOKUP returns the value of a cell found by performing a horizontal table lookup.
HLOOKUP searches the first row (known as the index row) in the range T for the numeric or string value which ``matches'' X, and returns the value N rows beneath the matching cell.
If the ``range lookup'' parameter R is omitted or specified as ``True'' (1), the first row of T must be sorted in ascending order to produce a useful result. In this case, the largest value in the first row of T which is less than or equal to X will be considered a ``match.'' If X is less than the left-most value in the first row of T, the ``not available'' value (NA) is returned. If X is larger than all values in the first row of T, the right-most column of T is considered a match.
If the ``range lookup'' parameter R is specified as ``False'' (0), an exact match to one of the values in the first row of T is required. The ``not available'' value (NA) will be returned if an exact match is not found.
N is an offset value for the lookup table. In SpreadScript's native HLOOKUP function, the offset of the first row in T is 0 (zero-based indexing). In XLHLOOKUP the offset of the first row is 1 (one-based indexing) for compatibility with Excel's HLOOKUP. Otherwise, the two functions are identical.
Examples:
A | B | C | D | |
1 | Lodging | Meals | Airfare | Entertainment |
---|---|---|---|---|
2 | $85.00 | $30.00 | $698.00 | $25.00 |
HLOOKUP(``Meals'', A1..D2, 1, 0) = 30
XLHLOOKUP(``Meals'', A1..D2, 2, 0) = 30
XLHLOOKUP(``Meals'', A1..D2, 1, 0) = ``Meals''
HLOOKUP(``Meals'', A1..D2, 1, 0) = ``Meals'' (in Excel compatibility mode)
HLOOKUP(``Airfar'', A1..D2, 1, 0) = NA (not available)
HLOOKUP(``Airfare'', A1..D2, 1, 0) = 698
HLOOKUP(``Airfare'', A1..D2, 1, 1) = NA (not available, because ``Airfare'' is alphabetically less than ``Lodging'')
HLOOKUP(``Airfare'', A1..D2, 3, 0) = Error - HLOOKUP, row offset out of range
Excel function: XLHLOOKUP is compatible with Excel's HLOOKUP (In Excel compatibility mode, the ``XL'' prefix is not used.)