Quotes, captions, names, wishes & practical how-tos

Google Sheets & Drive

Google Sheets VLOOKUP: Syntax, Examples and Fixes

Lab notebook next to a screen showing a spreadsheet, the kind of table a Google Sheets VLOOKUP searches
Photo: MikeRun, CC BY-SA 4.0, via Wikimedia Commons

Google Sheets VLOOKUP searches the first column of a range for a value and returns a matching value from another column in the same row. It is the quickest way to pull a price, a name or a status from a lookup table into another sheet. This guide covers the syntax, practical examples and the fixes for the errors you are most likely to see.

Key takeaways

  • The syntax is VLOOKUP(search_key, range, index, [is_sorted]).
  • Use FALSE as the last argument for an exact match in almost every real-world case.
  • VLOOKUP only looks to the right of the search column; use INDEX with MATCH or XLOOKUP to look left.
  • Most #N/A errors come from stray spaces, text-versus-number mismatches or a missing key.

How Google Sheets VLOOKUP Works

The “V” stands for vertical: the function scans down the first column of the range you give it, finds the row that matches your search key, then moves across to the column number you specify and returns that cell. If nothing matches, you get an #N/A error.

=VLOOKUP(search_key, range, index, [is_sorted])
Argument What it means
search_key The value to look for, such as a product ID or a cell reference like A2.
range The table to search. The search key must be in its first column.
index Which column of the range to return, counting the first column as 1.
is_sorted Optional. TRUE allows an approximate match on sorted data; FALSE demands an exact match.

The official VLOOKUP help page from Google documents the same arguments and is worth bookmarking.

A Basic Exact-Match Example

Imagine a sheet named Products with IDs in column A, names in column B and prices in column C. On an Orders sheet, column A holds product IDs and you want the price next to each one.

  1. Click the cell where the first price should appear, for example B2 on the Orders sheet.
  2. Type the formula below.
  3. Press Enter, then drag the fill handle down to copy the formula to other rows.
=VLOOKUP(A2, Products!$A$2:$C$100, 3, FALSE)

The dollar signs lock the range so it does not shift when you copy the formula down. The 3 asks for the third column of the range, which is the price. Because the last argument is FALSE, Sheets only returns a row whose ID matches exactly.

Laptop on a desk showing data and charts, useful for checking Google Sheets VLOOKUP results
Photo: rawpixel, CC0, via Wikimedia Commons (source)

Approximate Match for Bands and Tiers

Setting the last argument to TRUE (or leaving it out) is useful for tiered tables such as commission rates or grade boundaries. The first column must be sorted in ascending order, and Sheets returns the row with the largest value that does not exceed your key.

=VLOOKUP(B2, $E$2:$F$6, 2, TRUE)

If the table is not sorted, the result can be wrong without any error message, which is why exact match is the safer default.

Fixing Common Google Sheets VLOOKUP Errors

#N/A: value not found

The key does not exist in the first column of the range. Check for trailing spaces, different spelling and numbers stored as text. Wrapping the key in TRIM often fixes imported data:

=VLOOKUP(TRIM(A2), Products!$A$2:$C$100, 3, FALSE)

Number versus text mismatch

The ID 1001 as a number will not match 1001 stored as text. Make both sides the same type, for example by converting the search key with VALUE or by reformatting the lookup column.

#VALUE!: index is too large

Sheets typically reports an out-of-range error here when the column number is larger than the number of columns in the range. Count the columns in your range again.

Wrong result with no error

Nearly always an approximate match on unsorted data. Add FALSE as the fourth argument.

Handling Missing Values with IFERROR

If a missing match is expected, wrap the formula so the sheet stays tidy:

=IFERROR(VLOOKUP(A2, Products!$A$2:$C$100, 3, FALSE), "Not found")

Use this thoughtfully. It hides every error, including genuine mistakes in your range, so test the plain formula first.

Looking Left: INDEX/MATCH and XLOOKUP

VLOOKUP cannot return a column to the left of the search column. Two alternatives solve that. INDEX with MATCH works in every spreadsheet app:

=INDEX(Products!B2:B100, MATCH(A2, Products!A2:A100, 0))

Google Sheets also offers XLOOKUP, which takes the search key, the column to search and the column to return as separate arguments:

=XLOOKUP(A2, Products!A2:A100, Products!B2:B100)

Both approaches survive inserted columns better than a hard-coded index number. If you are weighing the two spreadsheet tools, see our comparison of Google Sheets vs Excel.

Desk with a laptop and calculator for working with Google Sheets VLOOKUP formulas
Photo: AnonymousUnknown author, CC0, via Wikimedia Commons (source)

When you need several matching rows instead of one value, the FILTER function is the better tool. To summarize a lookup result by category, build a pivot table. For a whole column at once, wrap the formula in ARRAYFORMULA and give it a range as the search key.

Frequently Asked Questions

Can VLOOKUP search another sheet or file?

Yes. Reference another tab with Sheet2!A:C. For a different spreadsheet file, combine it with IMPORTRANGE, which asks you to allow access the first time.

Is VLOOKUP case sensitive?

No. It treats uppercase and lowercase letters as the same. Use a different approach, such as combining INDEX with MATCH and EXACT, if you need case-sensitive matching.

Why does VLOOKUP return the first match only?

It stops at the first row that matches. For every match, use FILTER or QUERY instead.

Should I use TRUE or FALSE?

Use FALSE when you want an exact match, which covers most lookups. Use TRUE only for sorted tiers or ranges.

Does VLOOKUP update automatically?

Yes. When the lookup table or the search key changes, the result recalculates.

Wrap-Up

Remember the four arguments, lock your range with dollar signs, default to FALSE and clean your data before blaming the formula. With those habits, a Google Sheets VLOOKUP stays reliable even as your tables grow.

Written byEditorial Team

Part of the MarkdownToGoogleDocs editorial team, sharing copy-ready words and practical Markdown and Google Docs how-tos.