next up previous contents index
Next: HMEAN Up: A Function Reference Previous: HEXTONUM

HLOOKUP

 

Syntax: @HLOOKUP(X, R, N)

X = a numeric or string value
R = a range
N = a numeric value

@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 R for the numeric or string value which ``matches'' X, and returns the value N rows beneath the matching cell.

If X is a string value, an exact match must be found or an error is returned.

If X is a numeric value, the index row must contain numeric values, sorted in ascending order, and the matching column is determined by the following rules:

  1. Strings and blank cells in the index row are ignored.

  2. If the first value in the index row is greater than X, an error is returned.

  3. Searching stops when a numeric value which is greater than or equal to X is found in the index row. If the value found is greater than X, the preceding column is the matching column.

  4. If there are no numeric values in the index row which are greater than or equal to X, the last column in R is considered to be the matching column.

Examples:

A B C D
1 Lodging Meals Airfare Entertainment
2 $85.00 $30.00 $698.00 $25.00

@HLOOKUP(``Meals'', A1..D2, 1) = 30

@HLOOKUP(``Airfar'', A1..D2, 1) = Error - @HLOOKUP, lookup failed to produce a match

@HLOOKUP(``Airfare'', A1..D2, 3) = Error - @HLOOKUP, row offset out of range


next up previous contents index
Next: HMEAN Up: A Function Reference Previous: HEXTONUM

NExS User's Guide, Version 1.4.5
Grey Trout Software
11 April 1999