Using VLOOKUP to retrieve data from an Excel table

Excel

VLOOKUP is a simple way to retrieve data from a table in Excel, but you must understand its limits. VLOOKUP works by looking down the left column of the table’s range until it finds a match for the lookup_value, then it looks across that row to the cell in the column you specify.

Download the Excel samples files for VLOOKUP

VLOOKUP uses this syntax,

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

 lookup_value is the value you are looking for in the left-most column of the table.

table_array is the array containing the data. The leftmost column is the column that will be searched.

col_index_num is the number of the column you want to retrieve data from.

[range_lookup]  is TRUE if you want to find a near match to the lookup_value or FALSE if you want to find an exact match. In most cases you will use FALSE.

Use VLOOKUP to find the row containing data in the leftmost column of a table and then retrieve data from that row.

Use VLOOKUP to find the row containing data in the leftmost column of a table and then retrieve data from that row.

In cell D7 the formula is

=VLOOKUP($D$4,$C$12:$F$18,4,FALSE)

VLOOKUP looks at the leftmost column, $C$12:$C$18, of the range $C$12:$F$18 for a match to Hawking, which is specified in $D$4. VLOOKUP looks for an exact match to Hawking since the [range_lookup] parameter is FALSE. It then looks to the right in the row containing Hawking to the fourth (4) column.

A VLOOKUP like this is simple to enter and works great for tables where the lookup_value is in the left column and the columns stay in the same numeric order. When you need more flexibility, checkout the INDEX MATCH combinations.

Download the Excel samples files for VLOOKUP

Share the power...