Formulas

How to Use INDEX MATCH in Excel with Multiple Criteria

Q: How do I handle #N/A in INDEX-MATCH?

Wrap the whole formula in IFERROR: =IFERROR(INDEX(..., MATCH(...)), "Not found"). For root-cause fixes before hiding the error, check for trailing spaces, numbers stored as text, or a missing 0 as the match type — these cause most #N/A issues.

Q: Can INDEX-MATCH look up across multiple sheets?

Yes — put the sheet name in the range reference: =INDEX(Prices!C:C, MATCH("Widget A", Prices!A:A, 0)). For lookups across many sheets or workbooks, consider consolidating the source data with Power Query first, because sheet-hopping lookups become hard to maintain past three source sheets.

By Sagnik Bhattacharya18 Mar 20268 min read

Coding Liquids blog cover featuring Sagnik Bhattacharya for INDEX-MATCH — The Superior Alternative to VLOOKUP, with lookup references, match arrows, and formula comparison cues.

Ask any experienced Excel user which lookup function they prefer, and most will say INDEX-MATCH. It is more flexible, more powerful, and more reliable than VLOOKUP — once you understand how the two functions work together.

Coming Soon

Complete Excel Guide with AI Integration

Master formulas, pivot tables, data analysis, and charts — with AI integration.

Learn more

This tutorial walks through the single-criterion pattern, multi-criteria lookups (both the classic array trick and the cleaner helper-column approach), two-way row-and-column lookups, last-match and range lookups, and when you should stop using INDEX-MATCH and move to XLOOKUP. Every section includes a real-world scenario and the beginner pitfall that breaks the formula in practice. There is also a dedicated Troubleshooting section that consolidates the six most common #N/A causes so you can fix a broken lookup in under a minute.

INDEX-MATCH works in Excel 2013, 2016, 2019, 2021, Excel 365, and Excel for Mac. It is also the right answer on shared workbooks where some colleagues are still on Excel 2019 — XLOOKUP needs Excel 2021 or Microsoft 365, so it will break for them. For the cluster index of every Excel formula we cover, see the Excel formulas hub.

Prerequisites

Excel 2013 or later on Windows or Mac, or Excel for the web. Microsoft 365 users get dynamic-array behaviour, which makes multi-criteria lookups easier (no Ctrl+Shift+Enter needed).
A dataset arranged as a single rectangular block with headers in row 1 and no merged cells in the lookup column. Merged cells are the single most common cause of mysterious #N/A errors.
Basic familiarity with cell references. Absolute references ($A$2:$A$100) matter once you start copying INDEX-MATCH formulas down a column — forget the dollar signs and the ranges shift.

How INDEX-MATCH Works

It is actually two functions working together:

MATCH finds the position (row number) of your lookup value inside a range — it returns an integer, not the value itself.
INDEX returns the value at that position from another range.

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

The third argument of MATCH is 0 for exact match. Using 1 (default) or -1 requires sorted data and returns the nearest match — almost always the wrong thing on transactional datasets, and a surprisingly common source of silent wrong answers.

Practical Example

Find the price of "Widget A" where product names are in column A and prices in column C:

=INDEX(C2:C100, MATCH("Widget A", A2:A100, 0))

MATCH finds "Widget A" in A2:A100 and returns its row position (say, 5). INDEX then returns the 5th value from C2:C100.

Real-world scenario. A Manchester wholesaler maintains a 4,800-row SKU price list. Sales reps paste 30–50 SKU codes into a pricing sheet every morning and need prices pulled across. INDEX-MATCH pulls each price in under a second; when the finance team added a new "Cost" column between Product and Price last quarter, nothing broke — with VLOOKUP, the col_index_num would have returned cost instead of price across every reference until someone noticed.

Beginner pitfall. The lookup range (A2:A100) and the return range (C2:C100) must be the same number of rows. Using A2:A100 with C2:C50 returns #REF! for any match found beyond row 50 — and no error at all for matches in the first 50 rows, which makes the bug easy to miss.

Why INDEX-MATCH Beats VLOOKUP

Advantage	Explanation
Look left	VLOOKUP can only return values to the right of the lookup column. INDEX-MATCH can look in any direction — useful when you need to return the product code given a product name in a later column. For a full comparison of all lookup options, see VLOOKUP vs XLOOKUP.
Column independence	VLOOKUP uses a `col_index_num` (e.g. `3`) that silently returns the wrong column when anyone inserts a column into the table. INDEX-MATCH uses direct range references, so inserting a column shifts the references correctly.
Performance	On large datasets, INDEX-MATCH is significantly faster than VLOOKUP because it only processes the lookup column and the return column, not every column in between. For datasets above about 500,000 rows with many lookups per workbook, consider Power Pivot instead.
Multiple criteria	Combine with multiple MATCH calls (or a boolean array) for two-dimensional or multi-criteria lookups — impossible with pure VLOOKUP without concatenation hacks.
Partial and reverse lookups	INDEX-MATCH handles wildcard lookups, return-last-match, and approximate-match on sorted data all with the same two-function pattern.

Pattern 1: Two-Criteria Lookup (Array Formula)

The classic multi-criteria pattern uses boolean multiplication to build an array of 1s and 0s:

=INDEX(D2:D100, MATCH(1, (A2:A100="North")*(B2:B100="Electronics"), 0))

Enter with Ctrl+Shift+Enter in Excel 2019 and earlier (Excel shows the result wrapped in curly braces). In Microsoft 365 and Excel 2021, just press Enter — dynamic arrays handle the calculation transparently.

Real-world scenario. A 12-store retail chain runs a sales ledger with columns Region, Category, Manager, Revenue. The regional manager needs "Electronics revenue in the North region" pulled into a weekly pack. Two-criteria INDEX-MATCH returns the answer without sorting or reshaping the ledger.

Beginner pitfall. If you forget Ctrl+Shift+Enter on Excel 2019, the formula returns the value for the first row of the range regardless of the criteria — looks like it worked, but every answer is wrong. To check: click the formula and look at the formula bar. If you do not see the curly braces {} around the whole formula on Excel 2019, re-press F2 then Ctrl+Shift+Enter.

Pattern 2: Helper Column (Cleaner Alternative to the Array Trick)

Shared workbooks with mixed Excel versions often break on Ctrl+Shift+Enter formulas — a colleague on Excel 2019 saves over the formula without the array entry, and every downstream cell starts returning the wrong answer. The helper-column pattern sidesteps this entirely:

Helper column E: =A2&"|"&B2
Lookup: =INDEX(D2:D100, MATCH("North|Electronics", E2:E100, 0))

The pipe (|) is a separator that will not appear naturally in the data — important because &-joining A=Can/B=non with A=Canon/B=(blank) would produce identical "Canon" strings and match the wrong row.

Real-world scenario. A UK logistics firm shares a courier-rate workbook between three analysts — one on Excel 365, two on Excel 2019. Switching from the array formula to a helper column eliminated three "why is my rate wrong?" Slack threads per week because the formula now entered the same way on every machine.

Beginner pitfall. Skipping the separator. =A2&B2 joins "North" + "East" to "NorthEast" — which then matches a region actually named NorthEast in a neighbouring row. Always use an unambiguous separator.

Pattern 3: Two-Way Lookup (Row and Column)

For data laid out as a matrix — months across, products down — use MATCH twice, once for the row position and once for the column position:

=INDEX(B2:F10, MATCH("Product A", A2:A10, 0), MATCH("Q2", B1:F1, 0))

INDEX with three arguments returns the intersection of a given row and column, like coordinates on a grid.

Real-world scenario. A finance team maintains an 8-product × 4-quarter revenue grid. A two-way INDEX-MATCH on the Executive Summary sheet pulls any product/quarter cell into the commentary without re-sorting the grid.

Beginner pitfall. The first INDEX argument (B2:F10) must exclude the header row and the label column — otherwise MATCH position 1 points to the header, not the first data row. If your result looks off by one, check that the INDEX range starts at the first data cell, not at A1.

Pattern 4: Return the Last Match

VLOOKUP returns the first match. When you need the most recent entry for a given value (last login date, last stock movement, last adjustment), use:

=INDEX(B:B, MATCH(2, 1/(A:A="SearchValue")))

The trick: 1/(A:A="SearchValue") produces an array of 1s (for matches) and #DIV/0! (for non-matches). MATCH with lookup value 2 and no match-type argument searches for the largest value ≤ 2 in the array — which, since no value exceeds 1, returns the position of the last 1. That is your last match.

Real-world scenario. An inventory tracker logs every stock movement in one append-only sheet. Returning the last movement for each SKU on the dashboard uses this pattern against a SKU column and a date column.

Beginner pitfall. This pattern needs an implicit array evaluation. On Excel 2019 and earlier, enter with Ctrl+Shift+Enter. On Microsoft 365, a bare Enter works because dynamic arrays handle it.

Pattern 5: Approximate / Range Match

For banded lookups — commission tiers, tax brackets, shipping bands — sort your lookup table ascending and use 1 as the match type:

=INDEX(CommissionRate, MATCH(SalesAmount, RevenueBand, 1))

MATCH with type 1 returns the position of the largest value less than or equal to the lookup value. If your bands are 0, 5000, 10000, 25000 and SalesAmount is 7500, MATCH returns position 2 (the 5000 row) — which is the band that applies.

Real-world scenario. A sales ops analyst pays commission on a five-band schedule. Sorting the band column ascending and using the type-1 match gives the correct commission rate for any sales amount without a dozen nested IFs.

Beginner pitfall. Forgetting to sort the lookup column ascending. If the bands are not sorted, MATCH-type-1 returns arbitrary and wrong answers with no error message — and the error is easy to miss because most lookups still look plausible. The advanced formulas guide walks through a similar pattern with LOOKUP and XLOOKUP's match_mode argument.

Worked Example: Lookup Dashboard for a 1,200-SKU Catalogue

Suppose you maintain a product catalogue in a Table named tblProducts with columns SKU, Name, Category, Supplier, UnitCost, LastUpdated. A "Lookup" sheet takes a user-typed SKU in B2 and needs to show six attributes.

=INDEX(tblProducts[Name],     MATCH($B$2, tblProducts[SKU], 0))
=INDEX(tblProducts[Category], MATCH($B$2, tblProducts[SKU], 0))
=INDEX(tblProducts[Supplier], MATCH($B$2, tblProducts[SKU], 0))
=INDEX(tblProducts[UnitCost], MATCH($B$2, tblProducts[SKU], 0))
=INDEX(tblProducts[LastUpdated], MATCH($B$2, tblProducts[SKU], 0))

Because the lookup uses Excel Tables with structured references, adding new SKUs to the Table automatically extends the lookup range — no absolute references to update, no range to re-bless. Wrap each formula in IFERROR(..., "SKU not found") to handle typos gracefully.

When to Keep INDEX-MATCH vs Move to XLOOKUP

If everyone on your team is on Microsoft 365 or Excel 2021+, XLOOKUP is simpler, faster to write, and handles not-found values natively. But INDEX-MATCH is still the right answer when:

You have colleagues on Excel 2019 or earlier — XLOOKUP does not exist there and your formulas will show #NAME? on their machines.
You inherit a workbook where INDEX-MATCH is already used consistently — rewriting to XLOOKUP invites regressions without adding capability.
You need the explicit two-way INDEX(..., row, col) syntax for matrix lookups — XLOOKUP's nested form works but reads less cleanly.
Performance on very large ranges matters and every millisecond counts — INDEX-MATCH with a single MATCH cached in a helper cell is faster than XLOOKUP's compound call.

For new formulas on a Microsoft 365–only workbook, use XLOOKUP. For everything else, INDEX-MATCH remains the professional choice — see VLOOKUP vs XLOOKUP for the full migration matrix.

Common Mistakes

Mismatched lookup and return ranges. A2:A100 for the MATCH lookup, C2:C50 for the INDEX return — half the matches silently return #REF!.
Forgetting 0 as the third MATCH argument. Omitting it or passing 1 on unsorted data returns the wrong row with no error.
Relative references that shift when copied. Forgetting the dollar signs on the lookup range causes the range to drift as you copy down — use $A$2:$A$100 or an Excel Table with structured references.
Wrong Ctrl+Shift+Enter behaviour. Entering the multi-criteria pattern with a bare Enter on Excel 2019 returns row 1's value regardless of criteria.
Trusting approximate match on unsorted data. MATCH type 1 on unsorted bands returns arbitrary positions without throwing an error.

Troubleshooting

#N/A even though the value is clearly there. Most common cause: a trailing space, or a non-breaking space (CHAR(160)) on values pasted from a web page. Fix: add TRIM to the lookup column via a helper column, or rewrite the MATCH as MATCH(TRIM(B2), TRIM(A2:A100), 0) entered with Ctrl+Shift+Enter. If the values look right but still fail, check the data type — numbers stored as text never match actual numbers. Select the column → Data → Text to Columns → Finish to coerce text-numbers to real numbers.
#REF! appears only for some rows. The return range is shorter than the lookup range. If MATCH finds a value at position 78 but the return range only has 50 rows, INDEX returns #REF!. Fix: make both ranges the same size, or use full-column references (A:A, C:C) if you can tolerate the slight performance cost.
Formula returns the first row's value regardless of criteria. On Excel 2019 and earlier, the two-criteria boolean pattern needs Ctrl+Shift+Enter to evaluate as an array. Fix: click the formula, press F2, then Ctrl+Shift+Enter. You should see curly braces {} wrap the formula in the formula bar. On Microsoft 365, this issue does not occur.
#N/A when the lookup value looks numerically identical. Dates are stored as serial numbers; a "date" typed as text in the lookup cell will never match a real date in the lookup column. Fix: select the lookup column → Number Format → Short Date; check whether one side is displayed aligned-left (text) and the other aligned-right (number). Use =ISNUMBER(A2) on both sides to confirm.
Sorted approximate-match returns wrong band. MATCH type 1 requires the lookup column sorted ascending. If it is unsorted, or sorted descending, matches drift arbitrarily and most answers look plausible. Fix: sort the band column ascending, or switch to -1 with descending data. For tax brackets and commission tiers, ascending + type-1 is the conventional pairing.

Frequently Asked Questions

Is INDEX MATCH better than VLOOKUP?

Yes, for everything except the simplest single-criterion lookup where the lookup column is leftmost and the table structure will never change. INDEX-MATCH handles left-lookups, survives column insertions, is faster on large datasets, and supports multi-criteria patterns natively.

Should I use INDEX-MATCH or XLOOKUP in 2026?

If your entire team is on Microsoft 365 or Excel 2021+, XLOOKUP is the right default — it is simpler and handles not-found values natively. Keep INDEX-MATCH for shared workbooks where anyone is still on Excel 2019 or earlier, because XLOOKUP will return #NAME? on their machines.

Why does my multi-criteria INDEX-MATCH return the wrong row?

On Excel 2019 and earlier, the boolean multiplication pattern must be entered with Ctrl+Shift+Enter. Without it, Excel returns the first row's value regardless of criteria. Check the formula bar — you should see curly braces {} around the whole formula if it was entered correctly. On Microsoft 365, dynamic arrays handle this transparently.

How do I handle #N/A in INDEX-MATCH?

Wrap the whole formula in IFERROR: =IFERROR(INDEX(..., MATCH(...)), "Not found"). For root-cause fixes before hiding the error, see the Troubleshooting section above — most #N/A issues come from trailing spaces, numbers stored as text, or a missing 0 as the match type.

Can INDEX-MATCH look up across multiple sheets?

Yes — put the sheet name in the range reference: =INDEX(Prices!C:C, MATCH("Widget A", Prices!A:A, 0)). For lookups across many sheets or workbooks, consider consolidating the source data with Power Query first; sheet-hopping lookups become hard to maintain past three source sheets.