VLOOKUP (exact match).
Using VLOOKUP to locate an exact match in a data table, and return the value from a corresponding column.
Where/when to use the technique
Using VLOOKUP with an exact match is ideal to look up product numbers, customer data or other data where there is a unique key to find in a data table.
VLOOKUP’s parameters (in English)
- =VLOOKUP([what should it look for],[where is the table to look in],[which column should it return a value from],[guess if it can’t find a match])
Key points with VLOOKUP and exact matches
- The final parameter for VLOOKUP with an exact match must be set to FALSE.
- The first column of your data does NOT need to be sorted in ascending order. (VLOOKUP will keep looking until it finds a match or figures out there is no match.)
- The column to return is the column of the table, not the column of the spreadsheet (if your VLOOKUP table starts in column B, then 1 refers to column B, as it is the first column in the table.)
- VLOOKUP exact matches are NOT case sensitive. (Looking up dog in a table will return Dog.)
- We can nest formulae into the “what to look for” section of the VLOOKUP formula.
- If VLOOKUP cannot find a match it will return #N/A.
Common VLOOKUP #N/A causes
- There is a space at the end of the value being looked up or the value in the table
- One side of the lookup value or table is text, while the other is numbers
- Will return the value from the second column of the table where the record in column A is equal to 5000.
- If the exact value of 5000 is not in the first column of the table, it will return #N/A
Lesson notes are only available for subscribers.