INDEX MATCH in Excel: 5 Real-World Lookup Patterns That Still Beat VLOOKUP

Why INDEX MATCH Still Earns Its Place

XLOOKUP is now the default recommendation, and for a simple one-key lookup it deserves to be. But INDEX MATCH is not dead. It runs in every version of Excel from 2007 onward, it survives older corporate builds and shared workbooks, and for grids and tiered rate tables it is often the shorter formula. Understanding it also makes every other lookup function easier to debug.

The core idea is two steps: MATCH finds where a value sits inside a range, and INDEX returns what sits at that position.

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

Everything below is a variation on that one line.

Sample data used throughout

Assume this table lives on a sheet named Orders, in A2:E500, with headers in row 1:

Pattern 1: Look Up to the Left

This is the classic VLOOKUP killer. You have a customer name and need the Order ID, but Order ID is column A while Customer is column B. VLOOKUP cannot look backwards without physically rearranging the data.

With cell H2 holding the customer name:

=INDEX($A$2:$A$500, MATCH($H2, $B$2:$B$500, 0))

MATCH returns the row position of the customer inside B2:B500; INDEX then pulls the value at that same position from column A. The 0 at the end is match type exact and must be typed explicitly — Excel defaults to 1 (approximate), which is a very common silent error.

Pattern 2: Two-Way Lookup in a Price Grid

When your data is a matrix instead of a list, you need two coordinates. Say you keep quarterly pricing on a sheet:

Headers sit in B2:E2, products in A3:A5, and values in B3:E5. Build a small query grid with quarter labels in B9:E9 and product names in A10:A12, then put this in B10:

=INDEX($B$3:$E$5, MATCH($A10, $A$3:$A$5, 0), MATCH(B$9, $B$2:$E$2, 0))

Drag it right and down. The mixed reference B$9 locks the header row but lets the column slide, while $A10 locks the product column but lets the row slide. That single formula fills the whole query block.

Pattern 3: Tiered Rates with Approximate Match

Commissions, tax brackets, shipping bands and grade scales are not exact matches — they are ranges. You want the rate that applies to a value, not a value that equals it.

With the thresholds in A3:A7 and rates in B3:B7:

=INDEX($B$3:$B$7, MATCH($F3, $A$3:$A$7, 1))

Match type 1 returns the position of the largest value that is less than or equal to the lookup value. Two rules make or break this pattern:

• The threshold column must be sorted ascending. Unsorted data returns quietly wrong answers, not errors.
• Always include a 0 row at the top. Otherwise anything below your first threshold returns #N/A instead of the minimum rate.

Pattern 4: Matching on Two Criteria

Sometimes one key is not enough — you need the tracking number for a specific customer *and* product. Multiply two comparison arrays inside MATCH:

=INDEX($E$2:$E$500, MATCH(1, ($A$2:$A$500=$H2)*($B$2:$B$500=$I2), 0))

Each comparison produces an array of TRUE/FALSE. Multiplying them coerces the result to 1 (both true) or 0, and MATCH(1, ..., 0) finds the first row where both conditions hit.

In Excel 2021 and Microsoft 365 this works with a normal Enter. In older versions you must confirm it with Ctrl+Shift+Enter so it is stored as an array formula (you will see curly braces in the formula bar).

Performance tip: on tables with tens of thousands of rows, array multiplication gets slow. Add a helper column =A2&"|"&B2 and use a plain exact match:

=INDEX($E$2:$E$500, MATCH($H2&"|"&$I2, $F$2:$F$500, 0))

Same answer, a fraction of the calculation cost.

Pattern 5: Pull a Whole Row (and Find the Last Match)

Setting the third argument of INDEX to 0 tells Excel to return the entire row rather than a single cell:

=INDEX($A$2:$E$500, MATCH($H2, $A$2:$A$500, 0), 0)

In Microsoft 365 the result spills across five columns, which is ideal for building a detail card above a list. In Excel 2019 and earlier, select five cells across, type the formula and press Ctrl+Shift+Enter.

Bonus: the last matching record

A normal MATCH always finds the *first* hit. To get the most recent one — say the latest shipment for a customer — wrap a LOOKUP position finder inside INDEX:

=INDEX($E$2:$E$500, LOOKUP(2, 1/($A$2:$A$500=$H2), ROW($A$2:$A$500)-ROW($A$2)+1))

The 1/(...) trick builds an array of 1s and #DIV/0! errors, and LOOKUP ignores the errors and lands on the last 1.

Pitfalls Worth Memorising

• Forgetting the 0. Omitting the match type makes MATCH approximate, and on unsorted data it returns plausible-but-wrong numbers.
• Numbers stored as text. A number in column B and the same number typed as text in your lookup cell will never match. Clean with TRIM and check with ISNUMBER before blaming the formula.
• Whole-column ranges. $A:$A inside a 200,000-row workbook is a real speed problem. Bound your ranges to the actual data.
• No error handling. Wrap anything user-facing in =IFERROR(..., "Not found") so a missing key does not ripple into every downstream total.
• Wildcards still work. With match type 0, MATCH("*"&$H2&"*", $B$2:$B$500, 0) finds a partial text match — handy for description searches, useless for numbers.

The Bottom Line

Use INDEX MATCH when you need to look left, resolve a row-and-column grid, apply a tiered rate, match on more than one field, or return an entire record. Use XLOOKUP when you have a plain single-key lookup and you are certain everyone opening the file has Microsoft 365. Learning both costs an afternoon and pays back every time you inherit a workbook built by someone else.