The VLOOKUP function lets you search the leftmost column for a value and return another value on the same row in a column you specify.
VLOOKUP stands for vertical lookup and your data (table_array) must be organized into records, a record on each row. To search records excel must look vertically in the data array. HLOOKUP stands for horizontal lookup but that is for another post.
Formula in cell C3:
The VLOOKUP function uses lookup value AA-1611 in cell B3 and searches Table1 (cell range B6:E18) in the leftmost column. A matching value is found on row 16.
The third argument is which column you want to fetch the corresponding value from. In this example column 4 is entered and £30.00 is returned in cell C3.
The fourth and last argument lets you choose between approximate and exact match, in this case FALSE meaning EXACT match.
The fourth argument allows you to choose if you want APPROXIMATE or EXACT match. You want, in most cases, to use the EXACT match however the default value is APPROXIMATE match. So make sure you know what you are doing.
This example below shows you the difference between EXACT and APPROXIMATE match.
You must have your leftmost column sorted in an ascending order or you may get incorrect results.
Approximate match is useful if you want to find which group or range your value belongs to. The example above shows that value 158 is between 100 and 200 so the return value is 15.
This example shows you how to search multiple tables. The image below shows two excel tables, table10 and table20.
Formula in cell E3:
The INDIRECT function lets you use a cell value as the table_array argument, allowing you to search multiple tables.
INDEX + MATCH
The VLOOKUP function is that it can only look for a value in the leftmost column. If you don't have your values in the leftmost column you have two options, rearrange your table or use another method, shown below.
The INDEX and MATCH function allows you to search any column in a table and return a value in any column on the same row. This formula is so versatile that I actually prefer INDEX + MATCH over the VLOOKUP function.
Formula in cell C3:
The MATCH function lets you look for a value in a column and return the relative position of the found value in the array. AA-1611 is found on row 11 in cell range C6:C18
This extensive function reference list provides detailed information including syntax, arguments, return values, and examples for many of the functions used in Excel formulas. Click on headers to sort table.
Articles with the 'VLOOKUP' Function
The following 6 articles have formulas that contain the VLOOKUP function.