The 1-2-3 @VLOOKUP function will look up data in another part of the spreadsheet and enter it into the cell the @VLOOKUP formula is found in. It can be used to find entries in tables, or automate data selection for formulas or macros. In the article's example, @VLOOKUP checked out the Part#, went to the Part# price list, found the Unit Price associated with the Part#, and entered it into the UnitPr column.
The syntax of the command is as follows:
@VLOOKUP(x;range;column-offset)
X can be either a value or text, cell address or name, or a formula that returns a value or text. In the example, it was E4, the cell address containing the part number.
Range represents the location of the vertical lookup table. Range can be any range address or range name. In our example, the range of the lookup table was specified from B20 to C22.
Column-offset represents the position the desired column of data occupies in the range. The column-offset is a number, starting with zero, incrementing by 1. Zero represents the first column, 1 the second, and so on. In our example, the range comprised a two column table. The first column had the part number and the second column the unit price. We wanted to display the unit price, which was in the second column, so we set the offset as 1.
The first column of the lookup table must be in ascending order if values are used. Entries in the first column should be unique - no duplicates.
Related @formulas
@HLOOKUP looks up a value in a horizontal lookup table.
@INDEX finds a value when you specify offset numbers for both the column and the row.
@CHOOSE replaces a lookup table that requires only one row.