The VLOOKUP function enables you to search for a value in one column and return another from the same row in a different column. As it works from top to bottom, the ‘V’ stands for vertical.
Arguably, it’s the most famous function in Excel and one of the first you’ll learn. It has been ever-present since the program’s birth in 1985!
This article explains how to use it and the options it provides.
· Download Workbook
· The Basics
∘ Standard VLOOKUP
∘ Only the First Match Is Returned
∘ Column Positions Matter
∘ Right to Left Lookup (1)
∘ Right to Left Lookup (2)
· Approximate Match
· Case Sensitivity
∘ Case-Insensitive Lookup
∘ Case-Sensitive Lookup
· Final Words
This file contains all the examples explained in the article.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
All arguments are required except
lookup_value—the value to look for in the first column of the
table_array—the range containing the value to search for and the one to return.
col_index_num—the column number of the
table_array containing the return value.
[range_lookup]—specify a logical value to return either an approximate or exact match:
TRUE/1 — returns an approximate match.
FALSE/0 — returns an exact match.
This is an optional argument so will default to TRUE if omitted.
We have a customer table and want to find the email address of Eric Rampy.
lookup_valuereferences I11, which contains Eric Rampy.
table_arrayis the entire customer data in H15:L24.
col_index_numis 4, which corresponds to the Email column.
[range_lookup]is FALSE to ensure an exact match is found.
- The formula in J11 returns firstname.lastname@example.org.
Only the First Match Is Returned
VLOOKUP only returns the first matching row, so any duplicates below it will be ignored.
In this example, the original email address for Eric Rampy is no longer returned as it is preceded by an identical value in the Name column.
Column Positions Matter
lookup_value must be in the leftmost column of the
table_array and the return value to the right of it.
As the Address column is now first, Eric Rampy does not appear so a #N/A error occurs.
Right to Left Lookup (1)
Sometimes it’s not appropriate to change the column order of the existing data. To circumvent this, one option is to duplicate the return column and place it after the lookup one.
Here, we want to search for the email address and return the name.
lookup_array begins in the Email column and the final value is returned from Name2, which sits at the end referencing the values of Name.
Right to Left Lookup (2)
A better option is to combine VLOOKUP with CHOOSE.
=CHOOSE(index_num, value1, [value2], ...)
CHOOSE allows you to reference each column individually to generate an array. The value is then retrieved from the column set in VLOOKUP’s
The lookup column doesn’t necessarily have to be referenced first, but it does require an
index_num of 1.
This method has another benefit as well: if you add a new column to the table, the formula won’t break.
The examples hereon use Excel Tables instead of cell references to reference data. If you’re not familiar with these, check out Exceljet’s guide.
[range_lookup] to TRUE returns an approximate match. Despite being the default, it’s unlikely you’ll want this the majority of the time. It’s useful for numeric data though, where typically there is more variation so you’re less interested in finding a specific figure.
In this example, the table contains grade boundaries that correspond to degree classifications.
We want to find the degree classification for 66%. An exact match would return a #N/A error as it doesn’t exist in the table. However, using an approximate match means it finds the nearest value that is less than the one being looked up. This is 60%, which is the minimum threshold for Upper Second-Class Honours.
Your data must be sorted in ascending order for an approximate match to work. Otherwise, you will get unusual results.
VLOOKUP will result in an error when something has gone wrong. Here are some examples:
#REF! — the
col_index_num is greater than the number of columns in the
#N/A — the
lookup_value is not found in the
#N/A — the
lookup_value is misspelt or has an extra space.
#N/A — the
lookup_value is missing a data type.
#N/A — the
lookup_value mismatches the
table_array entry’s data type.
Steve Jobs is a person but there’s also a movie with the same name. These are not considered equal, even though the content is identical in each cell.
To trap these errors, wrap the IFERROR function around the VLOOKUP statement to replace them with something else.
The following will return Not Found:
This will return an empty string:
VLOOKUP supports wildcards, which are characters that take the place of text, so you can widen the scope of your lookup.
- An asterisk (*) represents any number of characters.
- A question mark (?) represents one character.
- A tilde (~) is the precursor for a literal character, in cases where * or ? are looked up.
These examples show how each wildcard can be used and the results they return. The original data is housed at the top, and each lookup table contains a few values to search for so the corresponding numbers are returned.
- Ex*ent returns 5 (Excellent) as the asterisk fills in for ‘cell’.
- E?cel?en? returns 5 (Excellent) as the question marks fill in for ‘x’, ‘l’, and ‘t’, respectively.
- Excellent~~ returns 8 (Excellent~) as the second tilde is the literal character.
By default, VLOOKUP performs a case-insensitive lookup, meaning it finds the first match in a range irrespective of whether it’s uppercase, lowercase or mixed case. To circumvent this, it can be used in conjunction with EXACT.
EXACT checks two text strings to see if they’re the same case. TRUE is returned if they are — FALSE if not.
A table called tblColours contains a set of colour names alongside corresponding numbers. We want to look up GOLD from N12 and return the adjacent number.
A standard VLOOKUP formula retrieves the number of GOLD from tblColours. As Gold appears before GOLD, 3 is returned.
This time, the
lookup_value is TRUE while the
lookup_array uses CHOOSE to hold the EXACT statement and the Number column.
EXACT(N12,tblColours[Colour]) evaluates to an array of 10 logical elements, with the sixth being the sole TRUE value. This is the position of GOLD in the Colour column.
Now we know the lookup element, we can return the corresponding value in
value2, which is 6.
Congratulations! You’ve just learnt a useful function that will help you a lot on your Excel journey. Practice, experiment, and explore the different ways of using it. However, don’t get too comfortable.
I strongly recommend you move on to these once you’re ready for greater efficiency and flexibility.