How to Use VLOOKUP in Google Sheets
In Google Sheets, VLOOKUP searches down the first column of a range and returns a value from another column in the same row. The syntax is =VLOOKUP(search_key, range, index, FALSE). Here is how each part works, with examples.
VLOOKUP in Google Sheets does the same job as in Excel: it matches a value in one table to data in another — like pulling a price from a product list into an order sheet. The syntax is nearly identical, with one small naming difference. Once you learn its four parts, it becomes one of the functions you reach for most.
The syntax
=VLOOKUP(search_key, range, index, is_sorted)| Part | Meaning |
|---|---|
| search_key | The value you are looking for |
| range | The table to search; the key must be in its first column |
| index | Which column number to return the result from |
| is_sorted | FALSE (or 0) for an exact match; TRUE for approximate |
A simple example
If product codes are in column A and prices in column B (A2:B100), and the code you want is in D2:
=VLOOKUP(D2, A2:B100, 2, FALSE)The final FALSE forces an exact match — leave it off and Sheets does an approximate match that returns wrong results on unsorted data. Almost always use FALSE.
A real-world use case: pulling from another tab
The most common job for VLOOKUP is filling one sheet from a lookup table on another. Say your orders are on one tab with product codes in column A, and a separate tab named Prices holds codes in column A and prices in column B. Next to the first order, enter this and copy it down:
=VLOOKUP(A2, Prices!A:B, 2, FALSE)Each order now shows the matching price, and if a price changes on the Prices tab, every order updates on its own. Point the criteria at a cell — like D2 — instead of typing the code, and one formula becomes a reusable lookup box.
Exact match vs approximate match
| is_sorted | What it does | When to use |
|---|---|---|
| FALSE / 0 | Exact match; returns #N/A if not found | Almost always — codes, names, IDs |
| TRUE / 1 | Closest value not greater than the key; needs the first column sorted | Rare — sorted number ranges like tax bands |
Common errors
- #N/A — the key was not found. Usually a trailing space (fix with TRIM), a number stored as text, or the value simply is not there.
- #REF! — the index is larger than the range has columns.
- Wrong value returned — you left off FALSE, or the key is not in the first column of the range.
Limitations and the modern alternative
VLOOKUP can only look to the right, it needs the key in the first column, and it returns just the first match. Google Sheets also has XLOOKUP, which can look left, matches exactly by default, and does not break when you insert columns. For a two-way lookup, INDEX with MATCH is the flexible classic. Know Excel already? Compare with VLOOKUP in Excel, or read how INDEX MATCH works in INDEX MATCH in Excel.
Frequently asked questions
Why does my Google Sheets VLOOKUP return #N/A?
The search key was not found in the first column of the range. The usual causes are extra spaces (fix with TRIM), a number stored as text, or the value simply not being present. Wrapping the key in TRIM and matching data types usually fixes it.
Is VLOOKUP or XLOOKUP better in Google Sheets?
XLOOKUP is more flexible — it can return columns to the left, matches exactly by default, and survives inserted columns. VLOOKUP is still widely used and fine for simple left-to-right lookups on a stable table.
How do I VLOOKUP from another sheet in Google Sheets?
Put the sheet name and an exclamation mark before the range, like =VLOOKUP(A2, Prices!A:B, 2, FALSE). The formula works exactly the same way when the lookup table lives on a different tab.