How to Use VLOOKUP in Google Sheets (With Examples)
VLOOKUP looks for a value in the first column of a table and returns a value from another column in the same row. It's the classic way to answer questions like "what's the price of product P-103?" or "which department does this employee belong to?" without scrolling through hundreds of rows.
VLOOKUP syntax
=VLOOKUP(search_key, range, index, [is_sorted])- search_key – the value you're looking for, such as a product ID or a cell containing one.
- range – the table to search. VLOOKUP always searches the first column of this range.
- index – which column of the range to return, counting from 1. The first column of the range is 1, the next is 2, and so on.
- is_sorted – optional. Use
FALSEfor an exact match. If you leave it out, Sheets assumesTRUE(approximate match), which gives wrong results on unsorted data.
Rule of thumb: always type FALSE as the last argument unless you specifically need an approximate match. Forgetting it is the most common VLOOKUP mistake.
A step-by-step example
Say you have this product list in columns A to C:
| A | B | C | |
|---|---|---|---|
| 1 | Product ID | Product | Price |
| 2 | P-101 | Notebook | 4.50 |
| 3 | P-102 | Pen set | 7.90 |
| 4 | P-103 | Desk lamp | 24.00 |
| 5 | P-104 | Stapler | 9.25 |
To find the price of P-103:
=VLOOKUP("P-103", A2:C5, 3, FALSE)The result is 24.00. Sheets finds P-103 in the first column of A2:C5, then returns the value from the 3rd column of that range (Price).
In practice you'll usually point to a cell instead of typing the ID. If the ID you want is in cell E2:
=VLOOKUP(E2, $A$2:$C$5, 3, FALSE)
The dollar signs make the range absolute, so it doesn't shift when you drag the formula down to look up more IDs. Press F4 while the cursor is on the range to add them automatically.
Look up a whole column that keeps growing
If new products are added regularly, use an open-ended range so you never have to update the formula:
=VLOOKUP(E2, $A$2:$C, 3, FALSE)$A$2:$C means "from A2 down to the last row, in columns A to C".
How to fix common VLOOKUP errors
| Error | What it means | How to fix it |
|---|---|---|
#N/A | The search key wasn't found in the first column. | Check for typos and extra spaces (use Data → Data cleanup → Trim whitespace). Make sure numbers aren't stored as text in one place and as numbers in the other. |
#REF! | The index is larger than the number of columns in the range. | If your range is A:C, the index can only be 1, 2 or 3. |
#VALUE! | The index is less than 1 or isn't a number. | Use a column number starting at 1. |
| Wrong value, no error | You left out FALSE, so Sheets did an approximate match. | Add FALSE as the fourth argument. |
Show a friendly message instead of #N/A
Wrap the formula in IFNA:
=IFNA(VLOOKUP(E2, $A$2:$C, 3, FALSE), "Not found")VLOOKUP's big limitation (and the alternative)
VLOOKUP can only return values to the right of the search column. If you need to search by Product name (column B) and return the Product ID (column A), VLOOKUP can't do it.
Google Sheets also supports XLOOKUP, which can look in any direction and defaults to an exact match:
=XLOOKUP("Desk lamp", B2:B5, A2:A5)This returns P-103. XLOOKUP works the same way in Excel; see our XLOOKUP guide for all its options.
Frequently asked questions
Can VLOOKUP search another sheet?
Yes. Put the sheet name before the range with an exclamation mark: =VLOOKUP(E2, Products!$A$2:$C, 3, FALSE). If the sheet name has spaces, wrap it in single quotes: 'Product list'!$A$2:$C.
Is VLOOKUP case-sensitive?
No. "p-103" and "P-103" are treated as the same value.
What if there are several matches?
VLOOKUP returns only the first match it finds from the top. To get all matches, use the FILTER function instead, for example =FILTER(C2:C, A2:A=E2).