Excel Dashboards and Reports: The VLOOKUP Function
Building cool Excel dashboard components won’t do you any good if you can’t effectively manage your data models. The VLOOKUP function is the king of all lookup functions in Excel. The purpose of VLOOKUP is to find a specific value from a column of data where the leftmost row value matches a given criterion.
Take a look at this figure to get the general idea. The table on the left shows sales by month and product number. The bottom table translates those product numbers to actual product names. The VLOOKUP function can help in associating the appropriate name to each respective product number.
To understand how VLOOKUP formulas work, take a moment to review the basic syntax. A VLOOKUP formula requires four arguments: VLOOKUP (Lookup_value, Table_array, Col_index_num, Range_lookup)
Lookup_value: The Lookup_value argument identifies the value being looked up. This is the value that needs to be matched to the lookup table. In the example in the figure, the Lookup_value is the product number. Therefore, the first argument for all the formulas shown in the figure references column C (the column that contains the product number).
Table_array: The Table_array argument specifies the range that contains the lookup values. Here are a couple points to keep in mind with this argument. First, for a Table_array to work, the leftmost column of the table must be the matching value. For instance, if you’re trying to match product numbers, the leftmost column of the lookup table must contain product numbers.
Second, notice that the reference used for this argument is an absolute reference. This means the column and row references are prefixed with dollar ($) signs. This ensures that the references don’t shift while you copy the formulas down or across.
Col_index_num: The Col_index_num argument identifies the column number in the lookup table that contains the value to be returned. In the example in the figure, the second column contains the product name (the value being looked up), so the formula uses the number 2. If the product name column was the fourth column in the lookup table, the number 4 would be used.
Range_lookup: The Range_lookup argument specifies whether you’re looking for an exact match or an approximate match. If an exact match is needed, you’d enter FALSE for this argument. If the closest match will do, you’d enter TRUE or leave the argument blank.