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.
To see how the VLOOKUP function works, take a moment to review the basic syntax. A VLOOKUP
function requires four arguments:
➤ 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.