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