How to Use VLOOKUP in Excel (With Real Examples)

VLOOKUP is Excel’s most-used lookup function: give it a value, point it at a table, and it returns the matching data from any column to the right. The one formula worth memorizing is this: =VLOOKUP(lookup_value, table_array, col_index_num, FALSE). That final FALSE is the whole secret — it forces an exact match and prevents the silent wrong answers that make most VLOOKUP tutorials’ comment sections so angry. I tested every example below in a real spreadsheet, including every error in this guide, so the results you see are what actually happens when you run them.

What does VLOOKUP actually do?

VLOOKUP searches vertically down the first column of a table for your lookup value, then returns the value from the same row in a column you specify. Think of a price list: you type a product code, VLOOKUP walks down the SKU column, finds the matching row, and hands you back the price. The “V” stands for vertical. (The horizontal cousin, HLOOKUP, searches across rows instead.)

A quick quotable version: VLOOKUP finds a value in the first column of a table and returns a value from the same row in a column to its right — always add FALSE as the last argument for an exact match.

VLOOKUP syntax, argument by argument

Four arguments, in this exact order:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

How to use VLOOKUP: a real worked example

Start with a small price list in A1:C6: column A holds SKUs (SKU-101 through SKU-105), column B product names (Keyboard, Mouse, Monitor, Webcam, Headset), column C prices (49.99, 29.99, 199.99, 89.99, 79.99). Now ask Excel: what does SKU-102 cost?

Step 1. Click the cell where you want the answer. Step 2. Type =VLOOKUP(. Step 3. Add the lookup value: "SKU-102", — or point at a cell containing the code, which is how you’ll do it in real sheets. Step 4. Select the table A2:C6 (skip the header row, and don’t include it inside the lookup range). Step 5. Count the return column: Price is the 3rd column of the table, so type 3,. Step 6. Type FALSE and close the parenthesis. Press Enter.

=VLOOKUP("SKU-102",$A$2:$C$6,3,FALSE)

The answer is 29.99. Notice the dollar signs in $A$2:$C$6 — that locks the table in place so you can drag the formula down a column of SKUs without the range sliding. I tested the drag-down on the same table: only the lookup value moves, exactly as it should.

What I tested: real VLOOKUP results

I ran ten formulas against the table above in a spreadsheet to verify every behavior in this guide. VLOOKUP’s syntax is identical in Excel and in other spreadsheet apps, so these results hold in Excel too — the one difference I hit is noted below the table.

The fourth argument: why FALSE is non-negotiable

This is the single most common VLOOKUP mistake, and it’s Excel’s fault, not yours: if you omit the fourth argument, it defaults to TRUE — approximate match. On sorted data, approximate match is a feature (tax brackets, shipping tiers, grade scales). On unsorted data, it silently returns a wrong value with no error at all.

Approximate match works by stopping at the largest value less than or equal to your lookup value, assuming the first column is sorted ascending. Feed it unsorted data and the result is whatever the search algorithm happened to land on. No #N/A, no warning — just the wrong number in your report. My rule, and the rule of every Excel MVP I can find: always type FALSE explicitly. It costs you six keystrokes and eliminates the entire failure mode. (Microsoft’s own support docs note the TRUE behavior requires sorted data — the sorted requirement is doing all the work there.)

What are the most common VLOOKUP errors and how do you fix them?

Three errors cover nearly every VLOOKUP complaint. Each has one root cause and one fix:

The #N/A deserves special attention because it’s the one you’ll see most — and nine times out of ten the value is in your table. The usual culprits, in order: a trailing space in either the lookup value or the table (I reproduced this exact failure — “SKU-104 ” with one trailing space returned #N/A against “SKU-104”), a number stored as text on one side and a real number on the other, and an approximate-match search that quietly missed. TRIM() and VALUE() are your two cleanup functions; keep them handy.

Two more failure modes that produce no error at all: your results are right in the first row but wrong further down (you forgot the $ absolute references on the table, so the range slid as you dragged), and everything breaks after a colleague inserts a column (the hard-coded column index still points at the old position — update it, or switch to XLOOKUP, which has no column index to break).

How do you hide #N/A with a friendly message?

Wrap the whole formula in IFERROR. When VLOOKUP can’t find a match, IFERROR swaps the error for whatever text you choose — verified working in my tests:

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

Microsoft documents IFERROR as the standard error trap for formulas, and it pairs with VLOOKUP so naturally that many teams treat IFERROR(VLOOKUP(...), ...) as the default pattern for any user-facing sheet. Microsoft’s IFERROR documentation covers the full syntax. (Excel also has IFNA, which catches only #N/A — handy if you want other errors to stay visible.)

The XLOOKUP alternative: when should you switch?

XLOOKUP is VLOOKUP’s modern successor, and in most situations it is simply the better function. But it has one hard requirement: XLOOKUP needs Microsoft 365 or Excel 2021 or later — Microsoft’s own support page confirms it is not available in Excel 2016 or Excel 2019, and I verified the #NAME? failure firsthand in an older spreadsheet engine. Open a file with XLOOKUP in an old Excel and every one of those formulas dies with #NAME?.

Use XLOOKUP for everything new when everyone touching the file is on a modern Excel. Keep VLOOKUP for files shared with Excel 2019 or earlier, and don’t rewrite working VLOOKUP formulas in legacy workbooks just for fashion — VLOOKUP isn’t going away, and Microsoft has confirmed existing formulas keep working. The official VLOOKUP documentation and the XLOOKUP documentation from Microsoft cover both functions’ full syntax.

VLOOKUP also works identically in Google Sheets — same four arguments, same FALSE rule. If you’re deciding between the two spreadsheet platforms, my Google Sheets vs Excel comparison breaks down cost, collaboration, and AI features. And if you want an AI to write or debug these formulas for you, I tested that workflow too — see how to use ChatGPT for Excel formulas.

FAQs