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

VLOOKUP

 

Syntax: @VLOOKUP(X, R, N)

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

@VLOOKUP searches the first column (known as the index column) in range R for the numeric or string value which ``matches'' X, and returs the value N columns to the right in the row where the match is found. The index column is searched from top to bottom. If the column contains numeric values, they must be in ascending order, and cannot contain an empty 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 matching row is determined by the following rules:

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

  2. If the first value in the index column 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 column. If the value found is greater than X, the preceding row is the matching row.

Examples:

A B
1 Lodging $85.00
2 Meals $30.00
3 Airfare $698.00
4 Entertainment $25.00

@VLOOKUP(``Meals'', A1..B4, 1) = 30

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

@VLOOKUP(``Airfare'', A1..B4, 2)) = Error - @VLOOKUP, column out of range


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

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