next up previous contents index
Next: HMEAN, HARMEAN Up: A. Function Reference Previous: HEXTONUM   Contents   Index


HLOOKUP, XLHLOOKUP

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.)


next up previous contents index
Next: HMEAN, HARMEAN Up: A. Function Reference Previous: HEXTONUM   Contents   Index
SpreadScript User's Guide, Version 1.2
Grey Trout Software
02 March 2003