VLOOKUP Formula in Excel: How to Use It with Examples

VLOOKUP is the Excel formula beginners meet first — and the one that saves the most time once it clicks. It does one job: find a value in the first column of a table, then return information from another column on the same row. Looking up prices, names, grades, or stock levels from a big list is exactly what it was built for. This guide explains the formula piece by piece, walks through a real example, and fixes the errors everyone hits.

The VLOOKUP syntax

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

• lookup_value — what you are searching for (for example, a product code like “P102”).
• table_array — the table to search in. The value must be in the FIRST column of this range.
• col_index_num — which column of the table to bring back, counting from the left of the table (1 = the first column itself).
• range_lookup — use FALSE for an exact match. This is the right choice almost every time. TRUE (or leaving it out) finds an approximate match and causes most beginner errors.

A worked example

Imagine a small price list. In cells A2:B6 you have product codes and prices:

Code (A)	Price (B)
P101	250
P102	400
P103	175
P104	520

In cell D2 you type the code you want (say P103), and in E2 you write:

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

Reading it aloud: “Find the value in D2 in the first column of A2:B6, and bring back the value from column 2 of that table, exact match only.” The result is 175 — the price of P103. Change D2 to P101 and the same formula returns 250. One formula, any code.

Lock the table with $ signs before copying down

If you copy the formula down a column, Excel shifts the table range too — A2:B6 becomes A3:B7, then A4:B8 — and lookups start failing. Prevent it by making the table reference absolute:

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

Quick way: click the A2:B6 part of the formula and press F4 to add the $ signs automatically. Note that D2 stays relative — that part SHOULD move as the formula copies down.

The three errors you will meet (and what each means)

• #N/A — the lookup value was not found. Check spelling, stray spaces (“P103 ” with a trailing space will not match “P103”), and whether the value really exists in the first column. Numbers stored as text also cause this.
• #REF! — your col_index_num is bigger than the number of columns in the table (asking for column 3 from a 2-column table).
• #VALUE! — usually a col_index_num of 0 or a negative number, or a lookup value longer than the cell limits. Column numbers start at 1.

A polite way to handle #N/A in a finished sheet is to wrap the formula: =IFERROR(VLOOKUP(D2,$A$2:$B$6,2,FALSE), “Not found”) — but learn the raw errors first, because IFERROR also hides real mistakes.

The rules VLOOKUP cannot break

• It only looks right. The lookup value must be in the first column of your table; VLOOKUP cannot return a value from a column to the LEFT of the lookup column. (INDEX + MATCH or XLOOKUP can.)
• It returns the first match. If the first column contains duplicates, you get the first one — every time.
• It is not case-sensitive. “P103” and “p103” are the same to VLOOKUP.
• Text versus numbers matters. A code stored as text will not match the same code stored as a number, even though they look identical.

VLOOKUP in Google Sheets

The same formula works in Google Sheets with identical syntax, which is why it is worth learning properly — it transfers. Our companion guide, Google Sheets Formulas: A Beginner’s List, covers it alongside the other formulas worth knowing there.

What about XLOOKUP?

Newer versions of Excel (Microsoft 365 and Excel 2021 onwards) include XLOOKUP, which fixes VLOOKUP’s main limits: it can look left, defaults to exact match, and handles “not found” with a built-in argument. If your version has it, prefer XLOOKUP for new work. But VLOOKUP remains everywhere — in older files, in shared spreadsheets, and in interview questions — so it is still the one to learn first.

Quick recap

=VLOOKUP(what, where, which column, FALSE). Always use FALSE for exact matches, lock the table with $ signs before copying, and read #N/A as “not found”, #REF! as “column number too big”, and #VALUE! as “bad argument”. Master those, and you have the most useful lookup formula in Excel.