How to Use VLOOKUP in Excel (With Real Examples)

VLOOKUP searches for a value in the first column of a range and returns something from a column to its right. The formula looks like this:

=VLOOKUP(D2, A2:B50, 2, FALSE)

That reads as: take the value in D2, find it in column A, and give me the matching value from column B — exact matches only. That single formula covers most of what people need VLOOKUP for. The rest of this guide explains each part, walks through two real examples, and fixes the five errors that trip everyone up.

What VLOOKUP actually does

Think of a product list with codes in one column and prices in the next. You have a code and you want its price. VLOOKUP is the function that goes and fetches it, instead of you scrolling and copying by hand.

The "V" stands for vertical, because it searches down a column. There is an HLOOKUP that searches across a row, but you will rarely need it — most spreadsheets are built with data running downward.

One rule matters more than any other: the value you are searching for must be in the leftmost column of the range you give it. VLOOKUP cannot look to the left. This limitation is the single reason many people eventually switch to INDEX MATCH.

The four parts of the formula

Part	Name	What it means
D2	Lookup value	The thing you are searching for — a code, a name, an ID.
A2:B50	Table array	The range to search. Its first column must contain the lookup value.
2	Column index	Which column of that range to return, counting the first column as 1.
FALSE	Match type	FALSE means exact match. TRUE means approximate. Use FALSE unless you know why you want TRUE.

The column index catches people out constantly. It is not the spreadsheet's column letter — it is the position inside your range. If your range starts at column C, then column C is 1, column D is 2, and so on.

The column index counts from the start of the range — not from column A.

Example 1: looking up a price

Say columns A and B hold a product list, and you have typed a product code into D2.

A — Code	B — Price
PRD-101	45.00
PRD-102	62.50
PRD-103	18.75

In E2, enter:

=VLOOKUP(D2, $A$2:$B$100, 2, FALSE)

Type PRD-102 into D2 and E2 returns 62.50.

Notice the dollar signs in $A$2:$B$100. They lock the range so it does not shift when you drag the formula down. Without them, row 3's formula quietly becomes A3:B101, row 4's becomes A4:B102, and rows start dropping off the bottom of your lookup range. This is the most common cause of a VLOOKUP that "works at the top and breaks further down."

The fastest way to add them: select the range inside the formula and press F4.

Example 2: when TRUE is the right answer

Approximate match has one genuinely useful job — assigning a value to a band. Commission tiers, grade boundaries, shipping brackets.

A — Minimum sales	B — Commission
0	0%
10,000	3%
25,000	5%
50,000	8%

=VLOOKUP(D2, $A$2:$B$5, 2, TRUE)

A sales figure of 31,000 returns 5%, because VLOOKUP walks down until it passes the value and takes the row before. For this to work, the first column must be sorted smallest to largest. Unsorted data with TRUE returns wrong answers silently — no error, just a quietly incorrect number, which is far worse than a visible failure.

Five errors and how to fix them

1. #N/A — the value was not found

Usually genuine: the lookup value is not in the first column. But check for hidden causes first — a trailing space, or a number stored as text. Wrap the lookup value in TRIM() to strip spaces:

=VLOOKUP(TRIM(D2), $A$2:$B$100, 2, FALSE)

To show something friendlier than #N/A, wrap the whole formula:

=IFERROR(VLOOKUP(D2, $A$2:$B$100, 2, FALSE), "Not found")

2. Numbers that look identical but do not match

A code typed as text ("102") will never match a code stored as a number (102). Excel shows a small green triangle in the corner of such cells. Select the column, then use Data → Text to Columns → Finish to force them back to numbers.

3. #REF! — the column index is too high

You asked for column 4 of a three-column range. Count the columns in your range and correct the index.

4. The formula returns the wrong row after inserting a column

Inserting a column inside your range shifts everything, but the hard-typed index number does not update. This is a design weakness of VLOOKUP, not a mistake you made — and the reason INDEX MATCH is more durable in sheets that keep changing.

5. It works in one cell and fails when dragged

The range was not locked. Go back and add the dollar signs, as in Example 1.

A single trailing space is enough to break an exact-match VLOOKUP.

VLOOKUP, INDEX MATCH, or XLOOKUP?

Function	Best for	Weakness
VLOOKUP	Quick lookups in a stable sheet	Cannot look left; breaks when columns move
INDEX MATCH	Sheets that change often	Two functions to learn instead of one
XLOOKUP	Everything, if you have it	Missing in older Excel versions and in Google Sheets

If you are on Microsoft 365, XLOOKUP replaces VLOOKUP outright and handles the left-lookup problem for you. If you share files with people on older versions, stay with VLOOKUP or INDEX MATCH — XLOOKUP will not calculate for them.

Does this work in Google Sheets?

Yes, identically. Same name, same four arguments, same behaviour. The only difference worth knowing: Google Sheets lets you apply a lookup down a whole column at once with ARRAYFORMULA, instead of dragging the formula into every row.

Common questions

Can VLOOKUP return more than one column?

Not on its own. Either write a separate VLOOKUP for each column you need, or switch to XLOOKUP, which can return a whole row.

Why does my VLOOKUP find the wrong record?

Almost always TRUE where FALSE was intended. Approximate match returns the closest lower value rather than admitting it found nothing.

Can I look up a value across two different sheets?

Yes. Reference the other sheet in the range: =VLOOKUP(D2, Products!$A$2:$B$100, 2, FALSE). If the sheet name contains a space, wrap it in single quotes: 'Product List'!$A$2:$B$100.

Is VLOOKUP being retired?

No. Microsoft has said it is not removing it. Billions of existing spreadsheets depend on it, and it still ships in every version.

The short version

• Use FALSE as the fourth argument unless you are deliberately matching a band.
• Lock the range with dollar signs before dragging the formula down.
• The column index counts from the start of your range, not from column A.
• The lookup value must sit in the range's first column — this is the rule VLOOKUP cannot bend.
• Wrap it in IFERROR for anything another person will open.

Get those five right and VLOOKUP stops being fragile. The next step is INDEX MATCH, which removes the left-lookup limitation entirely — the upgrade most people make once a sheet grows past a few hundred rows.