← Functions
vlookuplookupfunctionsbeginner

Excel VLOOKUP Function

How to use VLOOKUP in Excel to search a table and return matching values. Covers syntax, examples, common errors, and when to switch to XLOOKUP.

VLOOKUP searches the first column of a table for a value, then returns data from any column to the right of it. The catch most people run into: it performs an approximate match by default, which can return wrong results silently. More on that below.

Syntax

=VLOOKUP(lookup_value, table_array, column_index_num, [range_lookup])
ParameterRequiredDescription
lookup_valueYesThe value you want to find. Can be a number, text, or cell reference. Must match the data type in the first column of your table.
table_arrayYesThe table to search. VLOOKUP always looks in the first column of this range.
column_index_numYesWhich column to return a value from, counted from the left edge of table_array — not from column A of your worksheet.
range_lookupNoFALSE or 0 for exact match. TRUE or 1 for approximate match. Defaults to TRUE if you leave it out, which is almost always wrong.
⚠

Always specify the last argument. Omitting range_lookup triggers approximate match by default, which can return completely wrong results with no error message.

Basic Example

You have a product list with Item IDs in column A and prices in column B. To pull the price for a specific item:

=VLOOKUP(E2, A2:B100, 2, FALSE)
  • E2 is the Item ID you're looking up
  • A2:B100 is the table
  • 2 tells VLOOKUP to return the value from the second column (Price)
  • FALSE requires an exact match

VLOOKUP scans column A until it finds the value in E2, then returns whatever is in column 2 of that row.

How It Works

VLOOKUP searches the first column only. Your lookup values must be in the leftmost column of table_array. You can't point it at any other column.

Column numbers are relative. column_index_num counts from the left edge of your table range, not from column A. If your table starts in column D, then D = 1, E = 2, and so on.

It only looks right. VLOOKUP can only return values from columns to the right of the lookup column. If you need a value to the left, use XLOOKUP or INDEX/MATCH.

It returns the first match. If the lookup column has duplicates, VLOOKUP stops at the first one. To get all matching rows, use FILTER.

It's not case-sensitive. "Apple", "apple", and "APPLE" are treated as identical.

Exact Match vs. Approximate Match

For most lookups, you want FALSE. Use it whenever you're looking up an ID, a name, a code — anything with a specific expected value:

=VLOOKUP(A2, products, 3, FALSE)

Approximate match (TRUE) is for range-based lookups: tax brackets, commission tiers, letter grades. It finds the largest value in the table that's still less than or equal to your lookup value. For this to work, the first column of your table must be sorted in ascending order.

// Assign a letter grade — table must be sorted: 0, 60, 70, 80, 90
=VLOOKUP(C2, grade_table, 2, TRUE)
⚠

With approximate match, an unsorted table doesn't throw an error. It just returns wrong answers. Sort ascending or don't use TRUE.

Common Use Cases

Pull multiple fields from one table. Change only the column number to retrieve different data for the same lookup value:

=VLOOKUP(A2, employees, 3, FALSE)   // department
=VLOOKUP(A2, employees, 4, FALSE)   // salary
=VLOOKUP(A2, employees, 5, FALSE)   // start date

Merge data from two sheets. Add customer names to an order list using a customer table on Sheet2:

=VLOOKUP(B2, Sheet2!$A:$D, 2, FALSE)

Lock the table reference with $ so it doesn't shift when you copy the formula down.

Dynamic column lookup with MATCH. If columns might be added or reordered, use MATCH to find the column position instead of hardcoding it:

=VLOOKUP(H4, B5:E13, MATCH(H5, B4:E4, 0), FALSE)

MATCH returns the position of the column header in H5, so the formula won't break when columns move.

Handling #N/A Errors

#N/A means VLOOKUP couldn't find the lookup value. Common reasons:

  • The value genuinely isn't in the table
  • Extra spaces in the lookup value or the table (run TRIM on both)
  • A number stored as text, or text stored as a number
  • The table range doesn't cover all your data
  • The formula was copied and the table reference shifted (lock it with $)

To return something friendlier than #N/A, wrap with IFNA:

=IFNA(VLOOKUP(A2, products, 2, FALSE), "Not found")
=IFNA(VLOOKUP(A2, products, 2, FALSE), "")
✓

Use IFNA rather than IFERROR. IFERROR swallows every error type, so a typo in your column number or a broken reference will pass silently. IFNA only catches #N/A.

Known Limitations

LimitationWorkaround
Only looks rightXLOOKUP or INDEX/MATCH
Returns first match onlyFILTER
Column numbers break when columns are insertedXLOOKUP with column references, or wrap with MATCH
Approximate match requires sorted dataSort ascending, or use exact match
Not case-sensitiveINDEX/MATCH with EXACT

When to Use XLOOKUP Instead

If you're on Excel 365 or 2021, XLOOKUP is worth learning. The syntax is cleaner, the column number problem goes away, and the not-found value is built in:

// VLOOKUP
=IFNA(VLOOKUP(A2, products, 3, FALSE), "Not found")

// XLOOKUP
=XLOOKUP(A2, products[ID], products[Price], "Not found")

VLOOKUP still works fine and you'll encounter it constantly in inherited workbooks. For new formulas though, XLOOKUP is less fragile.

Related Functions

INDEX and MATCH in Excel: Two-Dimensional Lookups

VLOOKUP and HLOOKUP each search in one direction. Combine INDEX and MATCH and you can look up any value at the intersection of a row and column — no column number hardcoding required.

VLOOKUP and HLOOKUP Functions

Excel RAND and RANDBETWEEN Functions

Need random numbers for test data or simulations? RAND and RANDBETWEEN generate them instantly. Here's when to use each one.

How to Compare Multiple Values in Excel

Comparing two values in Excel is easy. Comparing three or more requires a different approach. Here are the formulas and tools that actually scale.