VLOOKUPto search for related information by row. For example, if you want to buy an orange, you can use
VLOOKUPto search for the price.
range, index, [
search_key: The value to search for in the first column of the range.
range: The upper and lower values to consider for the search.
index: The index of the column with the return value of the range. The index must be a positive integer.
is_sorted: Optional input. Choose an option:
FALSE= Exact match. This is recommended.
TRUE= Approximate match. This is the default if
Important: Before you use an approximate match, sort your search key in ascending order. Otherwise, you may likely get a wrong return value. Learn why you may encounter a wrong return value.
This is the value you search in the first column of the
To do a simple check: If your
This is the
To return a non-error value, your search key must be in the first column of the
To do a simple check: If your
Also called “Column number.” This is the index of the column in the
After you set up the range,
Tip: When you use
This is an optional input. The two available choices are
We strongly recommend you:
This is the value that
Basic VLOOKUP examples:
VLOOKUP on different search keys
VLOOKUP to find the price of an Orange and Apple.
When you use
VLOOKUP, you can use different search keys such as "Apple" and "Orange."
range. If you don’t want to fill a value for search keys, you can also use a cell reference, for example "G9."
=VLOOKUP("Orange", B4:D8, 3, FALSE)
Return value = $1.01
=VLOOKUP("Apple", B4:D8, 3, FALSE)
Return value = $1.50
=VLOOKUP(G9, B4:D8, 3, FALSE)
Return value = $1.50
VLOOKUP on different column indexes
VLOOKUPto find the quantity of Oranges in the second index column.
VLOOKUP, imagine that the columns of the
rangeare numbered from left to right and start from 1. To find the target information, you must specify its column index. For example, column 2 for quantity.
Find the quantity of oranges, which is the second column of the
=VLOOKUP(G3, B4:D8, 2, FALSE)
Return value = 5
VLOOKUPexact match to find an exact ID.
VLOOKUPapproximate match to find the approximate ID.
TRUEwhen you search for a best match, but not an exact match.
FALSE, it returns an exact match. For example, the fruit name for ID = 103 is "Banana." If there’s no exact match, you get a #N/A error. Due to its more predictable behavior, we recommend you use exact match.
=VLOOKUP(G6, A4:D8, 2, FALSE)
Return value = "Apple"
=VLOOKUP(G3, A4:D8, 2, TRUE)
=VLOOKUP(G3, A4:D8, 2)
Return value = "Banana"
Common VLOOKUP applications
VLOOKUPwhen your search key doesn’t exist. In this case, if you don’t want #N/A, you can use
IFNA()functions to replace #N/A. Learn more about IFNA().
=IFNA(VLOOKUP(G3, B4:D8, 3, FALSE),"NOT FOUND")
Return value = “NOT FOUND”
Tip: If you want to replace other errors such as #REF!, learn more about IFERROR().
VLOOKUP with multiple criteria
VLOOKUPcan’t be directly applied on multiple criteria. Instead, create a new helper column to directly apply
VLOOKUPon multiple criteria to combine multiple existing columns.
|1. You can create a Helper column if you use "&" to combine First Name and Last Name.||=C4&D4 and drag it down from B4 to B8 gives you the Helper column.|
|2. Use cell reference B7, JohnLee, as the search key.||
=VLOOKUP(B7, B4:E8, 4, FALSE)
Return value = "Support"
VLOOKUP with wildcard or partial matches
VLOOKUP, you can also use wildcards or partial matches. You can use these wildcard characters:
- A question mark "?" matches any single character.
- An asterisk "*" matches any sequence of characters.
VLOOKUP, you must use an exact match: "
|"St*" is used to match anything that starts with "St" regardless of the number of characters, such as "Steve", "St1", "Stock", or "Steeeeeeve".||
=VLOOKUP("St*", B4:D8, 3, FALSE)
Return value = "Marketing"
Returns an unexpected value: If you set
TRUE, but your first column in the range isn’t sorted numerically or alphabetically in ascending order, then change is_sorted to
- VLOOKUP gives the first match:
VLOOKUPonly returns the first match. If you have multiple matched search keys, a value is returned, but it may not be the expected value.
- Unclean data: Sometimes, values with spaces that trail and lead may seem similar but
VLOOKUPtreats them differently. For example, the following are different to
- " Apple"
- "Apple "
- If approximate or
TRUEis used and if the search key in
VLOOKUPis smaller than the smallest value in the first column, then
- If exact match or
FALSEis used, then the exact match of the search key in
VLOOKUPisn’t found in the first column. If you don’t want #N/A when the search key isn’t found in the first column, you can use the function IFNA().
rangewith a number bigger than the maximum number of columns of the
range. To avoid this, make sure you:
- Count the columns from the selected
range, not the entire table.
- Start to count from 1 instead of 0.
- Incorrectly input the text or the column name for the
- Entered a number smaller than 1 for the
indexmust be at least equal to 1 and smaller than the maximum number of columns of the
VLOOKUPcan only search in the search key column, when
index= 1, or columns that are further right.
index only accepts a number.
- You might have missed a quote in the search key when your
search_keyis text data.
|Use absolute references for
You should use:
You should not use:
This prevents unpredictable changes in the
|Sort the first column in ascending order when you use an approximate match, such as
||If you use an approximate match or
|Clean your data before you use
Before you use
To trim white space that leads and trails, you can use Data Data cleanup Trim whitespace.
|Don't store number or date values as text||
Make sure your date or number values in the first column of your