Subscriber only lesson.
Sign up to this course to view this lesson.
About this lesson
Learn to use VLOOKUP to find an exact match and return it or the corresponding value from another column.
Multiple versions of this lesson are available, choose the appropriate version for you:
Using VLOOKUP to find an exact match in a data table and return it or the corresponding value from another column.
When to use
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.
- Create a new worksheet
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.