VLOOKUP Returning the Wrong Value? Here's Why

VLOOKUP returning a wrong value is worse than an error, because nothing tells you it's wrong. In most cases, the formula is missing its fourth argument, so VLOOKUP does an approximate match and picks a nearby row. This guide explains the six reasons VLOOKUP is returning the wrong value and shows how to fix each one.

Why VLOOKUP returns a wrong value instead of an error#

VLOOKUP has four arguments, and the last one decides how it matches:

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

The key point: leaving the fourth argument out is the same as TRUE. Approximate match is meant for tier tables, like tax bands or discount levels. On a list of IDs, it returns a value from a different row instead of telling you the ID is missing.

Check these causes in order:

1. The fourth argument is missing or TRUE.
2. Approximate match on unsorted data.
3. Duplicate lookup values.
4. The wrong column number, often after a column was inserted.
5. The table range moved when the formula was copied.
6. Wildcard characters in the lookup value.

Cause 1: the fourth argument is missing#

Here is a product list sorted by ID. There is no product 104:

This formula has no fourth argument:

=VLOOKUP(104,A2:C7,3)

It returns 25, the price of the Mouse. VLOOKUP didn't find 104, so it stopped at the largest ID below it, 103, and returned that row's price. No error, wrong answer.

Add FALSE to force an exact match:

=VLOOKUP(104,A2:C7,3,FALSE)

Now the result is #N/A, which correctly tells you product 104 doesn't exist. You can then handle it with =IFNA(VLOOKUP(104,A2:C7,3,FALSE),"Not found"). The article on TRUE vs FALSE in VLOOKUP explains the two modes side by side.

Cause 2: approximate match on unsorted data#

Approximate match only works when the first column is sorted from smallest to largest. It doesn't read every row. It jumps through the list, assuming it's in order. On unsorted data, it can stop at the wrong place and return a value from an unrelated row. The answer can even change if someone re-sorts the list.

If you meant to use approximate match, for example for commission tiers, sort the first column in ascending order (Data > Sort A to Z). If you didn't, add FALSE. The guide to VLOOKUP approximate match shows when TRUE is the right choice.

Cause 3: duplicate lookup values#

VLOOKUP returns the first match it finds and ignores the rest. If ID 102 appears twice, once for an old price and once for a new one, VLOOKUP always returns the first row.

Check for duplicates with COUNTIF. In a spare column, type =COUNTIF($A$2:$A$7,A2) and fill down. Any result above 1 is a duplicate. Remove the old rows, or use a more specific key, such as ID plus date. If you really need every match, FILTER can return all of them.

Cause 4: the wrong column number#

The third argument is a plain number that counts columns from the left edge of the table. If you count wrong, you get a value from the next column over. In the table above, Product is column 2 and Price is column 3.

It also goes wrong when someone inserts a column. Say you add a Category column between Product and Price. Excel stretches the range to A2:D7, but the 3 stays a 3, so the formula now returns the category instead of the price.

Make the column number follow the header with MATCH:

=VLOOKUP(E2,$A$2:$D$7,MATCH("Price",$A$1:$D$1,0),FALSE)

MATCH finds "Price" in the header row and returns its position, so the formula keeps working after columns move. XLOOKUP and INDEX MATCH avoid column numbers completely. The VLOOKUP function guide lists every argument.

Cause 5: the table range moved when you copied the formula#

If the table range isn't locked, it shifts as you fill the formula down. In F2 you write =VLOOKUP(E2,A2:C7,3,FALSE). In F5, the copied formula reads =VLOOKUP(E5,A5:C10,3,FALSE). The first three products are no longer in the table. With FALSE you'd see #N/A, but with approximate match you'd get wrong values.

Lock the table with $ signs before you copy:

=VLOOKUP(E2,$A$2:$C$7,3,FALSE)

Select the range inside the formula and press F4 to add the $ signs (Mac: ⌘T).

Cause 6: wildcards in the lookup value#

With FALSE, VLOOKUP treats * and ? in the lookup value as wildcards. A wildcard stands for other characters: * for any number of characters, ? for exactly one. So looking up the code "A*" matches the first code that starts with A, not the literal text A*.

If your codes really contain * or ?, put a tilde in front to make them literal: look up "A~*" instead. You can do that with a formula: =VLOOKUP(SUBSTITUTE(E2,"*","~*"),$A$2:$C$7,3,FALSE).

If the problem isn't a wrong value but an error like #N/A, see why VLOOKUP is not working for spaces, text numbers and other causes. For job tests, the VLOOKUP interview questions cover these same traps.

Practise it#

Practise approximate and exact match VLOOKUP in a live spreadsheet: you'll see exactly when TRUE is right and when it returns the wrong row. Then fix broken lookups in the lookup errors lesson, where each formula is checked instantly. Or try the exercise on this page first.