How to Use VLOOKUP in Google Sheets (Examples and Every Error Fix)

Nearly every broken VLOOKUP in Google Sheets I’ve debugged came down to one of three things: a missing fourth argument, a space hiding inside the data, or a range that starts in the wrong column. The formula itself is short — four parts — and once you know what each part does and which mistake produces which error, you can build and fix one in seconds.

This guide builds a working VLOOKUP from a real price-list example, explains the exact-versus-approximate choice that most tutorials skip, and ends with the fix for every error the formula can throw.

What VLOOKUP Actually Does

VLOOKUP stands for vertical lookup. Give it a value — a product code, an ID, a name — and it searches for that value down the first column of a range you point it at. When it finds a match, it reads across that row and returns the value from a column you choose. Think of a restaurant menu: your eye finds the dish in the left column, then travels right to read the price on the same line. VLOOKUP mechanizes exactly that eye movement.

The syntax:

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

Step by Step: Your First Working VLOOKUP

Picture this sheet: product names in column A, SKUs in column B, prices in column C, stock levels in column D — data in rows 2 through 6. The SKU you care about is typed into cell F2, and you want its price.

1. Click the cell where the answer should appear — G2 here.
2. Type =VLOOKUP(F2, B2:D6, 2, FALSE).
3. Press Enter. The price for that SKU appears.

Why those exact pieces:

• The range starts at B because B is where the SKUs live — VLOOKUP only ever searches the first column of the range you hand it.
• The index is 2 because, counting inside the range, price sits in the second column (B = 1, C = 2).
• FALSE forces an exact match, so a wrong SKU errors instead of returning a neighboring row’s price.

Using the cell reference F2 instead of typing “SKU-103” directly is what makes the formula reusable — change F2 and the answer updates on its own.

Exact Match vs. Approximate Match

FALSE — exact match. VLOOKUP scans the whole first column and demands an identical value. No match means #N/A. This is what you want for IDs, SKUs, emails, and names — anything where “close enough” is wrong.

TRUE — approximate match. VLOOKUP expects the first column sorted ascending, walks down until it passes your value, then backs up one row and returns it. It’s built for value bands like grade thresholds, tax brackets, or commission tiers:

=VLOOKUP(85, A2:B6, 2, TRUE) returns “B”, because 85 lands between 80 and 90 and the formula stops on the row below it.

Run an approximate match against unsorted data and you won’t get an error — you’ll get a confidently wrong answer, which is worse. Whenever VLOOKUP returns a value that doesn’t match but doesn’t complain either, check the fourth argument first.

Every Error VLOOKUP Can Throw

#N/A: the key wasn’t found

Three usual suspects, in order of frequency:

1. A typo. The key in F2 doesn’t exactly match anything in the first column.
2. Invisible spaces. “SKU-103” and “SKU-103 ” (trailing space) are different values to VLOOKUP. Wrap the source in =TRIM() to strip leading and trailing spaces.
3. Numbers stored as text. The search key 10312 (a number) will never match the text “10312” — look for the green triangle markers and convert the column.

If a missing key is a normal outcome rather than a data problem, wrap the lookup so it fails gracefully: =IFERROR(VLOOKUP(F2, B2:D6, 2, FALSE), “Not found”).

#REF!: the index points past the range

The range B2:D6 has three columns; ask for index 4 and there’s nothing to return. Count from the range’s left edge, not from the sheet’s column letters. Our guide to the #REF! error in Google Sheets covers this VLOOKUP version plus the deleted-column variant that breaks lookups after a cleanup.

Wrong record, no error: duplicate keys

VLOOKUP stops at the first match it meets, top to bottom. Two rows sharing the same SKU means you silently get whichever comes first. If lookups keep returning the wrong record, make the keys unique — the three cleanup routes in removing duplicates in Google Sheets each fit a different size of mess.

The Limitations Worth Knowing

• Left-to-right only. The search column must be the leftmost column of the range. If the answer sits left of your key, VLOOKUP can’t reach it — that’s INDEX/MATCH or XLOOKUP territory.
• The index is a hardcoded number. If someone inserts a column inside the range next month, every formula pointing at it silently returns the wrong column (or #REF! if the index spills past the new edge).
• First match only. Covered above — duplicates are invisible until they aren’t.

The honest verdict: for a stable table with a right-ward lookup, VLOOKUP is fine and reads clearly. Reach for INDEX/MATCH or XLOOKUP when the layout shifts often or the lookup direction fights you.

Edge Cases Worth Knowing

• Case sensitivity: none. “smith” matches “Smith”. If you genuinely need case distinction, build a helper column with =EXACT() and look up against that.
• Wildcards work on text keys with exact match: =VLOOKUP(“Smith*”, A2:B20, 2, FALSE) matches the first Smith-anything.
• Formula parse error out of nowhere? Some locales use semicolons as argument separators instead of commas. If the exact formula from a tutorial refuses to enter, swap the separators.
• Tables that grow automatically. If the lookup table is fed by something live — responses arriving from a linked Google Form, for instance — use a whole-column reference like B:D instead of a fixed range, so new rows are included without ever editing the formula.

Frequently Asked Questions

Is VLOOKUP case-sensitive in Google Sheets?

No — “apple” and “Apple” match, and so do “SMITH” and “Smith”. Numbers and dates must match exactly, but text comparisons ignore case entirely. If your data genuinely needs case distinction, add a helper column built with EXACT() and point the lookup at it.

Why does my formula break when I drag it down?

The range is relative, so it slides down with the formula. By row ten, a range that started at B2:D6 is searching B11:D15. Lock it with absolute references — $B$2:$D$6, toggled with F4 — or use whole columns like B:D, which never move.

Should I learn VLOOKUP or XLOOKUP?

Both, but for different reasons. VLOOKUP is everywhere in existing sheets, so you need it to read and maintain what’s already built. XLOOKUP is the better choice for anything new: exact match by default, no column counting, and it can look leftward. Google Sheets supports both today.

What’s the difference between VLOOKUP and HLOOKUP?

Direction. VLOOKUP searches down the first column of a range; HLOOKUP searches across the first row — use it when your table is transposed, with labels running down the left side and data in rows. Nearly all real-world tables are vertical, so VLOOKUP covers almost every case you’ll meet.

Sources:

• VLOOKUP — Google Docs Editors Help
• How to use VLOOKUP in Google Sheets: a complete guide — Zapier
• VLOOKUP in Google Sheets: Complete Guide — Sheets Bootcamp