VLOOKUP

How VLOOKUP works

VLOOKUP searches the first column of a table from top to bottom, finds a match, then moves right to return a value from whichever column you specify.

Think of it like a phone book. You search by name (first column), then read across to get the phone number (your chosen column). VLOOKUP does this in milliseconds across thousands of rows.

The catch, and this trips up most people, is that VLOOKUP can only look to the right. Your lookup value must always be in the first column of your selected range. If your data is not structured this way, you need INDEX MATCH instead.

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

Arguments

VLOOKUP basic: find product price by code

You work in sales at TechDirect. A customer wants the price of product P-1042. Your catalog has 500 rows. One formula finds it instantly.

=VLOOKUP(G2,$A$1:$D$9,4,FALSE)

• •G2 → the product code you're looking up
• •$A$1:$D$9 → your product catalog (locked with $)
• •4 → return column 4 (the Price column)
• •FALSE → exact match only

VLOOKUP with IFERROR: handle missing values gracefully

Your lookup formula works perfectly until someone types an invalid product code. VLOOKUP returns #N/A and your report looks broken. Wrap every VLOOKUP in IFERROR. Every single one. No exceptions.

=IFERROR(VLOOKUP(E2,$A$2:$B$8,2,FALSE),"Not found")

• •E2 → the order's product code (the value being looked up)
• •$A$2:$B$8 → the catalog: codes in column A, prices in column B
• •If the code is found → returns the matching price
• •If the code is missing → returns "Not found" instead of #N/A

VLOOKUP across sheets: pulling data from another tab

Your employee list is on Sheet1. Salaries live on Sheet2 to keep them restricted. You need to pull each person's salary without combining the sheets.

=VLOOKUP(A2,Sheet2!A:E,2,FALSE)

• •A2 → the employee ID on Sheet1
• •Sheet2!A:E → the compensation table on Sheet2 (the Sheet2! prefix points to the other tab)
• •2 → return column 2 of Sheet2 (the Salary column)
• •FALSE → exact match: IDs must match exactly

Sheet1 D2 looks up E-1001 on Sheet2 and pulls back $72,000. The Sheet2! prefix tells VLOOKUP exactly which tab to search. The rest of the formula works identically to a same-sheet lookup.

VLOOKUP with TRUE: the one time approximate match makes sense

Commission tiers. Tax brackets. Grade scales. These are the only real cases where VLOOKUP with TRUE makes sense, where you want to find which range a value falls into, not an exact match.

Critical requirement: your lookup table must be sorted in ascending order. If it is not, VLOOKUP returns garbage.

=VLOOKUP(B4,$E$2:$F$5,2,TRUE)

• •B4 → the rep's monthly sales (the value being matched against the tiers)
• •$E$2:$F$5 → the tier table (locked with $ since the same range applies to every row)
• •2 → return column 2 of the tier table (the rate)
• •TRUE → approximate match: pick the largest tier ≤ the lookup value

The $ sign mistake: why your formula breaks when you copy it down

This is the most common VLOOKUP mistake in real workplaces. You write a perfect formula in row 2, drag it down to row 50, and the results look wrong. The table range shifted with the formula.

Watch it happen step by step. You type the formula in E2, then drag it down to fill E3 and E4. Because there are no $ signs anchoring the table_array, every drag shifts the lookup range one column to the right.

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

• •D2 → the code you're looking up
• •A2:B5 → the catalog range: rows 2 through 5 of columns A and B
• •2 → return column 2 (Price)
• •FALSE → exact match

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

Where people go wrong

1. 01
   Using TRUE instead of FALSE

   You add TRUE at the end, or leave it blank (which defaults to TRUE), and your formula returns results that look almost right but are completely wrong. This is especially dangerous because Excel gives you no warning.

   Fix: Always explicitly type FALSE as the fourth argument unless you specifically need approximate matching and your table is sorted ascending.
2. 02
   Forgetting $ signs when copying down

   You write a perfect formula in row 2 and drag it down. By row 10 the results are wrong or showing #REF! errors. The table range shifted with each row.

   Fix: Lock your table array with $ signs: =VLOOKUP(A2,$B:$D,3,FALSE). The $ makes those column references absolute.
3. 03
   Column index out of range

   You specify col_index_num as 5 but your table_array only has 4 columns. Excel returns #REF! and you wonder why a formula that looks correct is failing.

   Fix: Count the columns in your table_array carefully. If table_array is B:E that is 4 columns, so col_index_num cannot exceed 4.
4. 04
   Lookup value not in first column

   You want to look up by employee name but names are in column C and IDs are in column A. VLOOKUP can only search the first column of your range. If you set range to C:E it searches column C, but then it can't return column A because that would mean looking left.

   Fix: Restructure your data so the lookup column is first. Or switch to INDEX MATCH which can look in any direction.

Notes

• VLOOKUP is case-insensitive. "apple" and "APPLE" return the same result
• Wildcard characters (* and ?) work in lookup_value when range_lookup is FALSE
• For multiple matches VLOOKUP returns the first match only. It stops searching after the first hit
• VLOOKUP can handle up to 255 characters in the lookup_value argument
• Column index counting starts at 1, not 0
• In Excel 365 and Excel 2021 and later use XLOOKUP instead. It is more powerful and has no left-only limitation
• Approximate match (TRUE) requires the first column to be sorted ascending. Unsorted data returns wrong results silently

What to learn next

Now prove it