Why INDEX MATCH or XLOOKUP Returns the Wrong Value: 6 Causes

Q: How do I make a lookup return the last match instead of the first?

In Excel 2021 or Microsoft 365, use `=XLOOKUP(value,lookup_range,return_range,,0,-1)`. In any version, `=LOOKUP(2,1/(lookup_range=value),return_range)` returns the last match.

Q: Can VLOOKUP be case-sensitive?

Not on its own. Use `=INDEX(return_range,MATCH(TRUE,INDEX(EXACT(lookup_range,value),0),0))`, or in Microsoft 365, `=XLOOKUP(TRUE,EXACT(lookup_range,value),return_range)`.

Last updated: 3 October 2026 · Download: lookup-wrong-value-test-workbook.xlsx, with one sheet per cause, a broken formula and a fixed formula on each.

A lookup that returns #N/A is annoying, but at least you know something is wrong. A lookup that returns the wrong value is worse. The cell shows a believable price or name, nothing looks broken, and the mistake travels into reports and invoices.

This guide covers six ways INDEX/MATCH, VLOOKUP and XLOOKUP return a value from the wrong row or column without any error. Each section gives the broken formula, how to spot the problem, and the fix, including the XLOOKUP version. Every example is in the downloadable workbook: on each sheet, A6 holds the lookup value and B6 shows the answer you should get, so the red cell with the wrong answer is easy to compare.

A quick check for any lookup you don't trust

Before you look at the formula, check what MATCH actually found:

=MATCH(A6,H6:H10,0)

This returns the position of the match within the search range. Count down the range to that position and see whether it's the row you expected. Then confirm the return range starts on the same row as the search range. Those two checks catch causes 1 to 3 below.

Cause 1: MATCH is doing an approximate match

Sheet "1 Approx match" in the workbook.

MATCH has three arguments, and the third is easy to leave out. When it's missing, MATCH uses 1, approximate match: it returns the position of the largest value that is less than or equal to the lookup value, and it assumes the column is sorted.

In the example, product IDs 1001, 1002, 1004, 1005 and 1006 are listed, and you look up 1003, which doesn't exist:

=INDEX(J6:J10,MATCH(A6,H6:H10))     → 59     (the price of 1002)
=INDEX(J6:J10,MATCH(A6,H6:H10,0))   → #N/A   (correct: 1003 isn't there)

The broken formula doesn't warn you that 1003 is missing. It silently returns the price of the nearest smaller ID. If the column isn't sorted at all, approximate match can return a value from an essentially arbitrary row.

VLOOKUP has the same trap: leaving out its last argument, or setting it to TRUE, means approximate match.

Fix: always end MATCH with 0 and VLOOKUP with FALSE when you need an exact match. A missing item then shows #N/A, which you can handle deliberately:

=IFNA(INDEX(J6:J10,MATCH(A6,H6:H10,0)),"No such product")

XLOOKUP: its default is exact match, so a basic =XLOOKUP(A6,H6:H10,J6:J10) is safe from this cause. It only behaves this way if you set match mode to -1 (exact or next smaller) on purpose.

Use approximate match only where it's genuinely what you want: tax bands, commission tiers or grade boundaries, in a column sorted from smallest to largest.

Cause 2: the return range and the search range don't line up

Sheet "2 Offset ranges".

INDEX/MATCH works in two steps. MATCH finds a position in one range, and INDEX returns the value at that same position in another range. If the two ranges don't start on the same row, every result is shifted.

=INDEX(I5:I9,MATCH(A6,H6:H10,0))    → Monitor arm   (wrong)
=INDEX(I6:I10,MATCH(A6,H6:H10,0))   → USB hub       (correct)

MATCH finds P-103 at position 3 of H6:H10. The return range I5:I9 starts at the header row, so position 3 is row 7, which belongs to the product above. This usually happens when one range is selected including its header and the other without it, or after rows are inserted above the table.

Test: compare the first and last row numbers of both ranges. They must match exactly, such as H6:H10 with I6:I10.

Fix: correct the ranges, or convert the data into an Excel Table (Ctrl+T) and use column names such as tblProducts[Product]. Table columns always line up.

XLOOKUP: it returns #VALUE! if the two arrays are different sizes, which catches some of these mistakes. But two ranges of the same size that start on different rows, such as H6:H10 and I5:I9, fail silently with XLOOKUP too.

Cause 3: the key appears more than once

Sheet "3 Duplicates".

Lookups stop at the first match. That's correct when every key is unique, but many real tables aren't: price histories, status logs and transaction lists repeat the same code. In the example, P-101 appears three times, oldest first, with prices 24, 27 and 29. You want the current price:

=INDEX(J6:J10,MATCH(A6,H6:H10,0))   → 24   (the oldest price)

Test: =COUNTIF(H6:H10,A6) tells you how many times the key appears. Anything above 1 means the lookup is choosing one of several rows, and it's always the first.

Fix: search from the bottom up. In Excel 2021 and Microsoft 365, XLOOKUP has a search mode for this:

=XLOOKUP(A6,H6:H10,J6:J10,,0,-1)   → 29

The -1 means "search last to first". In any version of Excel, this LOOKUP formula returns the last match:

=LOOKUP(2,1/(H6:H10=A6),J6:J10)   → 29

It works because 1/(H6:H10=A6) turns matching rows into 1 and non-matching rows into #DIV/0! errors. LOOKUP searches for 2, can't find it, and falls back to the last numeric value in the list, which is the last match. It doesn't need to be entered as an array formula.

"Last" only means "latest" if the data is in date order. If it isn't, sort it first, or use MAXIFS to find the latest date and look that up instead.

Cause 4: a hard-coded column number in VLOOKUP

Sheet "4 Column number".

VLOOKUP's third argument is a column number counted from the left of the range. The formula =VLOOKUP(A6,H6:K10,3,FALSE) was written when Price was the third column. Then a Category column was inserted before Price. Excel widens the range to H6:K10, but it doesn't change the 3, so the formula now returns the category:

=VLOOKUP(A6,H6:K10,3,FALSE)   → Input   (the category, not the price)

Test: count the columns from the start of the range to the column you want, and compare with the number in the formula.

Fix: stop counting columns. Either look up the column number by its header:

=VLOOKUP(A6,H6:K10,MATCH("Price",H5:K5,0),FALSE)   → 45

or point directly at the Price column with INDEX/MATCH or XLOOKUP. Inserting columns then can't break the formula:

=INDEX(K6:K10,MATCH(A6,H6:H10,0))   → 45
=XLOOKUP(A6,H6:H10,K6:K10)          → 45

Cause 5: codes that differ only in upper and lower case

Sheet "5 Case".

MATCH, VLOOKUP and XLOOKUP are all case-insensitive: to them, KX-10 and Kx-10 are the same value. Usually that's helpful. But some systems use case to tell codes apart, such as product variants, short URLs and some account IDs. In the example, KX-10 is blue and Kx-10 is red:

=INDEX(I6:I10,MATCH(A6,H6:H10,0))   → Blue   (found KX-10 first)

Test: check whether the column contains codes that are identical apart from case. If it does, a normal lookup will always return the first of them.

Fix: compare with EXACT, which is case-sensitive:

=INDEX(I6:I10,MATCH(TRUE,INDEX(EXACT(H6:H10,A6),0),0))   → Red

EXACT returns TRUE only for the exactly matching code, and MATCH finds that TRUE. The inner INDEX(...,0) lets older versions of Excel evaluate EXACT across the range without entering an array formula. In Excel 2021 and Microsoft 365, the shorter version is:

=XLOOKUP(TRUE,EXACT(H6:H10,A6),I6:I10)   → Red

Cause 6: the lookup value contains * or ?

Sheet "6 Wildcards".

In an exact-match MATCH, VLOOKUP, COUNTIF or SUMIF, two characters have a special meaning: * stands for any number of characters, and ? for any single character. That's useful when you want it, but a problem when they're part of a real code. In the example, the code A*-7 is meant literally:

=INDEX(I6:I10,MATCH(A6,H6:H10,0))   → Cable tray   (matched AB-7)

MATCH reads A*-7 as "A, then anything, then -7", so the first code that fits, AB-7, wins.

Test: look for *, ? or ~ in your lookup values. Part numbers, dimensions such as 10*20 and some product names contain them.

Fix: put a tilde ~ in front of each special character so it's matched literally. SUBSTITUTE can do this for whatever value is in the cell:

=INDEX(I6:I10,MATCH(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A6,"~","~~"),"*","~*"),"?","~?"),H6:H10,0))
→ Adapter (any size)

The ~ is replaced first, so existing tildes aren't doubled up by the later replacements.

XLOOKUP: it doesn't treat * and ? as wildcards unless you set its match mode to 2, so =XLOOKUP(A6,H6:H10,I6:I10) returns the right answer here without any escaping.

Which causes affect which function

Cause	INDEX/MATCH	VLOOKUP	XLOOKUP (defaults)
1. Approximate match	If MATCH has no 0	If last argument isn't FALSE	Not affected
2. Misaligned ranges	Yes	No	Yes, if same size
3. Duplicate keys	Returns first	Returns first	Returns first (use search mode -1)
4. Hard-coded column number	No	Yes	No
5. Case	Ignores case	Ignores case	Ignores case
6. Wildcards	Yes	Yes	Not affected

XLOOKUP avoids three of the six by default, which is a good reason to use it where everyone opening the file has Excel 2021 or Microsoft 365. The Lookup Function Chooser helps you pick based on your version and table layout.

Checklist

When a lookup returns a value that looks wrong:

Does every MATCH end in 0, and every VLOOKUP in FALSE? (cause 1)
Run =MATCH(value,range,0) and count down. Is it the row you expected? Do both ranges start on the same row? (cause 2)
Does COUNTIF show the key more than once? You're getting the first match. (cause 3)
In VLOOKUP, does the column number still point at the right column? (cause 4)
Are there codes that differ only by case? (cause 5)
Does the lookup value contain *, ? or ~? (cause 6)

If the lookup returns #N/A rather than a wrong value, see Why VLOOKUP returns #N/A. The same data problems apply to INDEX/MATCH and XLOOKUP. To read a complicated lookup formula step by step, paste it into the Formula Explainer.

FAQ

Why does INDEX MATCH return the wrong value instead of #N/A? Usually because MATCH is missing its third argument and is doing an approximate match, or because the INDEX range and the MATCH range start on different rows. Both give a believable value from the wrong row instead of an error.

Is XLOOKUP more reliable than INDEX MATCH? By default, yes, in two ways: it uses exact match and it ignores wildcards. It still returns the first match when keys repeat, ignores case, and fails silently with shifted ranges of the same size. It also isn't available in Excel 2019 or earlier.

How do I make a lookup return the last match instead of the first? In Excel 2021 or Microsoft 365, use =XLOOKUP(value,lookup_range,return_range,,0,-1). In any version, =LOOKUP(2,1/(lookup_range=value),return_range) returns the last match.

Can VLOOKUP be case-sensitive? Not on its own. Use =INDEX(return_range,MATCH(TRUE,INDEX(EXACT(lookup_range,value),0),0)), or in Microsoft 365, =XLOOKUP(TRUE,EXACT(lookup_range,value),return_range).

Does the test workbook work in Google Sheets? Mostly. Upload it to Google Drive and open it with Google Sheets. The formulas on sheets 3 and 5 compare a whole range at once. If Google Sheets shows an error in those cells, wrap the formula in ARRAYFORMULA(...). Everything else works as it does in Excel.