Table of Contents
You can use the VLOOKUP function in Excel to look up some value in a range and return a corresponding value.
This function uses the following syntax:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
where:
- lookup_value: The value you want to look up
- table_array: The range of cells to search for the lookup value
- col_index_num: The column number that contains the return value
- range_lookup: TRUE = approximate match, FALSE = exact match
Notice that the last argument allows you to specify TRUE to look for an approximate match of the value you want to look up or FALSE for an exact match.
The default value is TRUE, but in most cases you will want to use FALSE because this tells Excel that you want to find an exact match of the value you’re looking for.
When using TRUE, the VLOOKUP function will often return unexpected and inaccurate results.
The following examples show the difference between using TRUE and FALSE in the VLOOKUP function with the following dataset in Excel:
Example 1: Using VLOOKUP with TRUE
Suppose we use the following formula with VLOOKUP to look up the team names in column D and return the corresponding value from the points column:
=VLOOKUP(D2, $A$2:$B$10, 2, TRUE)
The following screenshot shows how to use this formula in practice:
Since we specified TRUE for the last argument in VLOOKUP, we specified that Excel should look for “approximate” matches for the team names.
Notice that none of the points values returned in column E match the team names from column D.
Example 2: Using VLOOKUP with FALSE
Suppose we use the following formula with VLOOKUP to look up the team names in column D and return the corresponding value from the points column:
=VLOOKUP(D2, $A$2:$B$10, 2, FALSE)
The following screenshot shows how to use this formula in practice:
Since we specified FALSE for the last argument in VLOOKUP, we told Excel to look for exact matches for the team names.
Notice that each of the points values returned in column E match the team names from column D.
By using FALSE, we were able to accurately find the team names in the original dataset and return their corresponding points value.
Note: When using FALSE, Excel will return #N/A if an exact match is not found.