How to Use VLOOKUP in Google Sheets (Step by Step)

VLOOKUP in Google Sheets finds a value in the first column of a range and returns a value from a column to the right. The syntax is =VLOOKUP(search_key, range, index, [is_sorted]) — set the last argument to FALSE for an exact match and make sure the lookup and source values share the same data type. That one formula replaces hours of manual searching.

The syntax, argument by argument

=VLOOKUP(search_key, range, index, [is_sorted])

Google Sheets calls the fourth argument is_sorted; Excel calls the same argument range_lookup. The behavior is identical — only the name differs.

Always use FALSE for an exact match

Set the last argument to FALSE for almost every lookup. FALSE tells Sheets “find this exact value, or return an error.”

If you leave it out or use TRUE, VLOOKUP performs an approximate match. It assumes the first column is sorted and returns the closest value that is less than or equal to your search key. On unsorted data that produces silently wrong results — no error, just the wrong row’s value. That’s more dangerous than a visible #N/A, because nothing warns you the answer is wrong.

Rule of thumb: if you’re matching an ID, name, or code, always end the formula with FALSE.

A worked example: look up a product price

Suppose Sheet1 holds a product list in columns A to C.

You type a SKU into cell E2 and want the matching price in F2.

1. Click cell F2.
2. Type =VLOOKUP( and click cell E2 — the search_key.
3. Type a comma, select the range A2:C4, and press F4 (or type $) to lock it as $A$2:$C$4.
4. Type another comma, then 3 — the price sits in the third column of the range.
5. Type a final comma, then FALSE, and close the bracket.

=VLOOKUP(E2, $A$2:$C$4, 3, FALSE)

Type B200 into E2 and F2 returns 25. Change E2 to a SKU that isn’t in the list and you get #N/A — the correct signal that no match exists.

Locking the range matters: without the $ signs, copying the formula down shifts the search range, and later rows return the wrong values.

VLOOKUP only looks left to right

VLOOKUP searches the leftmost column of its range and returns a value from a column to the right. It cannot return a value from a column to the left of the lookup column. If your prices sit in column A and your SKUs in column C, VLOOKUP can’t help.

You have three fixes:

• Reorder the columns so the lookup field is leftmost — quick, but breaks if the source can’t be edited.
• Use XLOOKUP, which has no direction limit:

  =XLOOKUP(E2, C2:C4, A2:A4)

• Use INDEX/MATCH, the classic left-lookup workaround:

  =INDEX(A2:A4, MATCH(E2, C2:C4, 0))

XLOOKUP is the modern replacement, and Google Sheets supports it, so prefer it for new sheets.

Why you get #N/A — and how to fix it

Most VLOOKUP errors are data problems, not formula problems. The usual suspects:

• The value genuinely isn’t there — check spelling and stray spaces. =VLOOKUP(TRIM(E2), ...) removes trailing spaces that break a match.
• Text vs number mismatch — 100 (number) and "100" (text) are not equal to VLOOKUP.
• A value to the left — VLOOKUP can’t look left; use XLOOKUP or INDEX/MATCH.
• is_sorted left as TRUE — switch it to FALSE.

Common errors at a glance

Pull data from another sheet or file

Prefix the range with a sheet name to search a different tab:

=VLOOKUP(E2, 'Sheet2'!A:C, 3, FALSE)

To reach a completely separate spreadsheet, combine it with IMPORTRANGE — see how to use IMPORTRANGE. For the Excel equivalent of this guide, see how to use VLOOKUP in Excel.

VLOOKUP vs XLOOKUP vs INDEX/MATCH

Use VLOOKUP for simple left-to-right lookups on tables that won’t change. Use XLOOKUP when you need left lookups, a custom “not found” message, or a formula that survives inserted columns. Use INDEX/MATCH when you need compatibility with older Excel versions.

FAQ

Is VLOOKUP the same in Google Sheets and Excel?

Functionally, yes. Only the argument names differ — Sheets uses search_key, range, index, is_sorted; Excel uses lookup_value, table_array, col_index_num, range_lookup. The behavior is the same.

Can VLOOKUP search multiple sheets?

Not directly. Reference one sheet per formula, or use IMPORTRANGE to pull another file into your sheet first. XLOOKUP can return an array across ranges but not across separate files.

Why does my VLOOKUP return #N/A when the value exists?

Usually a hidden space, a text-vs-number mismatch, or the lookup value sitting in a column to the left of the data you want back.

Is there a better function than VLOOKUP?

For most new work, XLOOKUP is more flexible and safer because it defaults to an exact match. INDEX/MATCH is the go-to for older Excel files. VLOOKUP remains the most widely understood.

Does VLOOKUP work with partial matches?

Yes — when search_key contains wildcards. * matches any sequence of characters and ? matches one, for example =VLOOKUP("*Backpack*", A:C, 3, FALSE). Wildcards only work with exact match (FALSE).