Excel 2003: Using the VLOOKUP Function

When you have very large Nested IF Functions, you may consider using the VLOOKUP or HLOOKUP Functions, instead.

With a VLOOKUP Function, you can enter the data (like our grading scale) into a separate worksheet list and have Excel refer to the list when searching for a value to return. This way, you don’t have to type it all into a long, complicated IF Function. VLOOKUP stands for Vertical Lookup. You use a Vertical Lookup (or, VLOOKUP) when your lookup list contains your data in columns.

VLOOKUP and HLOOKUP are very similar functions; however, they differ in how they search your lookup list. So, we’ll talk about VLOOKUP first and then, in the next lesson, cover HLOOKUP.

The VLOOKUP Function searches for a value in the first column of a lookup list and returns a value in the same row from another column in the table array.

                                                VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

The sample above reflects a VLOOKUP Function. Now, let’s break this down:

VLOOKUP                            =              Name of the Function

Lookup_value                   =              What value are you looking for in the 1st column of your list

Table_array                        =              This is the range of your lookup list

Col_index_num                =              Of the columns in your list, which column contains the data you want the formula to display?

Range_lookup                   =              Either True or False (TRUE = Close Match / FALSE = Exact Match)

Now, let’s put that into action using our grading scale example. I’d like Excel to look at a student’s grade. And, based on the score they’ve received, I’d like Excel to display the corresponding letter grade from the lookup list.

Here’s what my formula would look like in Excel:

=VLOOKUP(Sheet1!B2,Sheet3!$B$1:$C$36,2,FALSE)