The VLOOKUP function scans vertically down the leftmost column of data, looking for a match to the input you provide. Upon finding a match, VLOOKUP returns a value from the given row, corresponding to a column you specify.
Note: HLOOKUP functions similarly, but it scans horizontally across the first row of data and returns a value from a given column, with respect to a corresponding row number.
For example, if your data looks like the data in screenshot below, then this formula, =VLOOKUP("associate",A1:B4,2), would return 135:
The three arguments required by VLOOKUP are Lookup Value, Table Array, and Column index. There is an optional fourth argument, Range Lookup.
- Lookup Value: The data you want to find. This can be text (enclosed in quotes) or it can be a reference to another cell.
- Table Array: A reference to a range of cells of at least 1 column of data. By default, the first column of data is used as the index to find the corresponding data for each row. Your data should be sorted in ascending order by the first column.
- Column Index: The corresponding column that contains the data you want to return.
- Range Lookup [optional]: A logical value, TRUE or FALSE. If this argument is omitted or TRUE the lookup returns the first closest match. If the argument is set to FALSE, lookup searches for an exact match.
Note: If you plan to copy your formula to use across more than one cell, you may wish to use absolute cell references for the table array so the addresses do not automatically adjust to a new range as the formula is copied. In our above example, the Table Array reference would become $A$1:$B$4.