How to Use VLOOKUP in Google Sheets
FALSE as the last argument to get an exact match.
What VLOOKUP does
Imagine a product table with IDs, names, and prices. You have an ID and you want the price. VLOOKUP finds the row with that ID and gives you back the price, so you do not have to scan the table by eye.
The "V" stands for vertical: it searches down the first column of your range.
VLOOKUP syntax
=VLOOKUP(search_key, range, index, [is_sorted])
search_key: the value you are looking for, such as"P-102"or a cell likeE2.range: the table to search. The search key must be in the first column of this range.index: which column of the range to return, counting from 1 at the left edge of the range.is_sorted:FALSEfor an exact match,TRUEfor an approximate match. When in doubt, useFALSE.
A step-by-step example
Here is a small product table in cells A1:C5.
| A | B | C | |
|---|---|---|---|
| 1 | ID | Product | Price |
| 2 | P-101 | Notebook | 4.50 |
| 3 | P-102 | Pen | 1.20 |
| 4 | P-103 | Backpack | 32.00 |
| 5 | P-104 | Stapler | 8.75 |
To find the price of P-102:
=VLOOKUP("P-102", A2:C5, 3, FALSE)
This returns 1.20. Here is why:
- Sheets looks down column A (the first column of
A2:C5) for"P-102". - It finds it in row 3.
- The index is
3, so it returns the value from the third column of the range, which is column C.
If you want the product name instead, change the index to 2.
Exact match vs. approximate match
The last argument controls how matching works.
FALSE(or0): find the exact value only. If it is not found, you get#N/A. This is what you want most of the time, such as IDs, names, and emails.TRUE(or left out): approximate match. The first column must be sorted in ascending order. This is used for things like tax brackets or grade ranges.
Leaving the last argument out is a common source of wrong results, because it defaults to approximate matching. Get in the habit of typing FALSE.
Common VLOOKUP errors and how to fix them
#N/A: "Did not find value"
- The value really is not in the first column. Check for typos.
- There are hidden spaces. Wrap your search key in
TRIM(). - One side is a number and the other is text. For example,
101and"101"do not match.
#REF!: "Column index is out of range"
Your index is larger than the number of columns in the range. If the range is A2:C5, the largest valid index is 3.
VLOOKUP cannot look to the left
VLOOKUP only returns columns to the right of the search column. If you need to look up an ID in column C and return a value from column A, use INDEX with MATCH, or XLOOKUP.
Make your formula safer
- Lock the range with dollar signs when you copy the formula down:
$A$2:$C$5. - Handle missing values with
IFERROR:=IFERROR(VLOOKUP(E2, $A$2:$C$5, 3, FALSE), "Not found").
Try it yourself
Use the product table above (A1:C5). Type your formula into the box, starting with =, then press Check.
Exercise 1: Find a price
Write a formula that returns the price of product P-104, using an exact match.
Exercise 2: Find a product name
Write a formula that returns the product name for ID P-103, using an exact match.
VLOOKUP alternatives
- XLOOKUP: more flexible, can look in any direction, and defaults to exact match.
- INDEX + MATCH: works to the left and is less likely to break when you insert columns.
- SUMIF: when you want a total for the rows that match, not one value.
- QUERY: when you want to filter and summarize many rows, not just fetch one value.
Syntax and behavior in this guide were checked against Google's VLOOKUP help page. See all guides.