MTSU Graduate Multimedia Development Center: VLOOKUP function in Excel 2007

VLOOKUP also called as vertical lookup is an excel function that can help you find specific information in large data tables and select the appropriate data based on given conditions.
Here is an example that demonstrates how to use VLOOKUP function.
Let us say we have percentage grades and want excel to automatically fill in the letter grades as shown below by looking up a mapping table.

Below is our mapping table.

Lets begin.

1. Create two tables.

The table on the left contains two columns namely, percentage grades and letter grades. You want excel to automatically lookup the table on the right and decide the letter grades. So, a percentage grade of 75% should have a grade of A-

Make sure:
1. The contents of percentage grade are in percentage format. By default this value is text. To change it to percentage format, select the cells before entering the value and go to Home tab -> Number -> click on % icon.
2. Similarly, change the first column in mapping table so that the values are in percentages and not in text format. Notice that in the mapping tables, the values are in ascending order.

2. Select cell B2 and type the following:

=vlookup(

what do you see?
Can you see a tool tip showing vlookup formula? Yes, this is the format for vlookup

=vlookup(lookup_value, table_array,col_index_num,[range_lookup])

In our case, lookup_value is the cell A2 which contains the percentage value, table_array is the table on the right of the mapping table. To select the table_array just select the entire table and reference absolutely the cells by pressing F4 button on keyboard. When you look at the second table, the percentages are in the first column and the letter grades which I want to display are in the second column. so, the col_index_num is 2 in this case.

So,
start typing out the formula.
Select cell B2. type

=VLOOKUP(A2,$G$2:$H$8,2) and hit enter.

A2 is the cell we need to lookup in the mapping table.

G2:H8 is the second table array. Just select the entire table to get G2:H8 and hit F4 button to get the $ signs for absolute referencing. 2 refers to the second column in the second table which we want to display.