Microsoft Office Tutorials and References

In Depth Information

**Excel Functions for Your Data Model**

Figure 11-7:
The VLOOKUP function finds the appropriate product name for each product number.

VLOOKUP basics

To see how the VLOOKUP function works, take a moment to review the basic syntax. A VLOOKUP

function requires four arguments:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

➤
lookup_value:
The value that you want to look up in the first column of the lookup table. In

Figure 11-7, the lookup_value is the product number. Therefore, the first argument for all the

formulas shown in Figure 11-7 references column C.

➤
table_array:
The range that contains the lookup table. In Figure 11-7, that range is D16:E22.

Please note that for the VLOOKUP function to work, the leftmost column of the table must be

the matching value. For example, if you’re matching product numbers, product numbers

must be in the first column of the lookup table. Also, the reference that you use for this

argument is an absolute reference. This means that the column and row references are prefixed

with dollar ($) signs — as in $G$2:$H$8. This ensures that the references don’t shift while you

copy the formulas down or across.

➤
col_index_num:
The column number from within the lookup table that contains the

matching value. In Figure 11-7, the second (column E) contains the product name, so the formula

uses the number 2. If the product name column were the fourth column in the lookup table,

the number 4 would be used.

➤
range_lookup: Optional.
You can specify whether you’re looking for an exact match for

your value or an approximate match. If an exact match is needed, type
FALSE
for this

argument. If the closest match will do, type
TRUE
or leave the argument blank.