next up previous contents index
Next: VSUM Up: A. Function Reference Previous: VECLEN   Contents   Index


VLOOKUP, XLVLOOKUP

Syntax: VLOOKUP(X, T, N[, R])
  XLVLOOKUP(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''

VLOOKUP returns the value of a cell found by performing a vertical table lookup.

VLOOKUP searches the first column (known as the index column) 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 column of T must be sorted in ascending order to produce a useful result. In this case, the largest value in the first column of T which is less than or equal to X will be considered a ``match.'' If X is less than the top-most value in the first column of T, the ``not available'' value (NA) is returned. If X is larger than all values in the first column of T, the bottom-most row 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 column 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 VLOOKUP function, the offset of the first column in T is 0 (zero-based indexing). In XLVLOOKUP the offset of the first column is 1 (one-based indexing) for compatibility with Excel's VLOOKUP. Otherwise, the two functions are identical.

Examples:

  B C D
2 Pressure Volume Density
3 0.02 749.00 1.34
4 0.05 298.79 3.35
5 0.87 16.23 61.63
6 1.00 16.87 59.28
7 1.07 12.29 81.35
8 1.11 14.01 71.38
       

VLOOKUP(0.9, B3..D8, 1) = 16.23

XLVLOOKUP(0.9, B3..D8, 2) = 16.23

XLVLOOKUP(1.1, B3..D8, 3) = 81.35

VLOOKUP(1.1, B3:D8, 3) = 81.35 (in Excel compatibility mode)

XLVLOOKUP(0.01, B3..D8, 3) = NA (not available, because 0.01 is less than 0.02)

XLVLOOKUP(0.9, B3..D8, 3, 0) = NA (not available, because an exact match is required)

Excel function: XLVLOOKUP is compatible with Excel's VLOOKUP (In Excel compatibility mode, the ``XL'' prefix is not used.)


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