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