How to Use INDEX MATCH in Excel (Complete Guide)

INDEX MATCH is the classic Excel lookup combo when you need a flexible exact match and your workbook cannot rely on XLOOKUP. Microsoft documents MATCH with 3 match modes (-1, 0, and 1), and that exact-match mode (0) is the one most INDEX MATCH formulas use.

If you want the fast version: MATCH finds the position, INDEX returns the value at that position, and together they can look left, right, up, or down depending on how you set the ranges. If you want the broader lookup decision tree first, start with Every Excel LOOKUP Explained. For modern alternatives, compare Excel XLOOKUP vs VLOOKUP and the dedicated Excel XLOOKUP guide.

Microsoft Excel worksheets contain up to 1,048,576 rows and 16,384 columns. That scale is one reason lookup formulas matter: once a table gets wide or deep, hard-coded column positions become more fragile than position-based lookup logic (Microsoft Support).

What INDEX MATCH does in Excel

INDEX MATCH separates lookup work into 2 parts: MATCH finds a row or column position, and INDEX returns the corresponding item. Microsoft documents 3 MATCH modes—-1, 0, and 1—so using 0 makes an exact-match formula explicit rather than relying on the function’s approximate-match default (Microsoft Support).

The INDEX MATCH vs VLOOKUP choice is simple in older workbooks: INDEX MATCH is more flexible. VLOOKUP only searches the first column of a range and returns to the right, while INDEX can return from any column or row you point at. That makes the formula easier to adapt when tables move.

A basic example looks like this:

=INDEX($C$2:$C$6,MATCH(F2,$A$2:$A$6,0))

Here is the logic in plain English:

• F2 contains the value you want to find.
• MATCH(F2,$A$2:$A$6,0) returns the row position of that value inside the lookup list.
• INDEX($C$2:$C$6,...) returns the value from the result list at the same position.

If your dataset has unique IDs, product names, or employee numbers, this is the lookup pattern to learn first. It is compact, readable once you know the rhythm, and widely compatible with older versions of Excel that do not include XLOOKUP.

How to build the basic INDEX MATCH formula

Microsoft documents 2 forms of INDEX—array and reference—and the common lookup pattern uses the array form (Microsoft Support). Build it inside-out: test MATCH first, then wrap its position in INDEX. This provides 2 observable checkpoints before the finished formula returns a value.

Start with the lookup value and the lookup range:

=MATCH(F2,$A$2:$A$6,0)

If F2 contains Pencil, this formula returns 2 when Pencil is the second item in A2:A6. That 2 is the position, not the value itself. Once you have that number, you can plug it into INDEX:

=INDEX($C$2:$C$6,MATCH(F2,$A$2:$A$6,0))

A few small habits make the formula much easier to maintain:

• Lock your ranges with $ so the references do not drift when copied.
• Keep the lookup range and return range the same size.
• Use exact match (0) unless you intentionally want approximate results.
• Test MATCH on its own before nesting it inside INDEX.

That last point saves a lot of time. If MATCH returns the wrong position, INDEX will faithfully return the wrong value too. Splitting the formula into 2 parts makes troubleshooting much easier than staring at one long formula and guessing where it went wrong.

How to do a two-way lookup with INDEX MATCH

A worksheet can contain 16,384 columns, making a hard-coded return position brittle in a wide matrix (Microsoft Support). A two-way lookup instead uses 2 MATCH functions—one for the row and one for the column—then gives both positions to 1 INDEX function to return their intersection.

Imagine a table where products run down the rows and months run across the columns. You want the sales value where the product and month intersect. The formula usually looks like this:

=INDEX($B$2:$E$6,MATCH(G2,$A$2:$A$6,0),MATCH(G3,$B$1:$E$1,0))

In that formula:

• G2 is the row lookup, such as a product name.
• G3 is the column lookup, such as a month.
• The first MATCH returns the row number.
• The second MATCH returns the column number.
• INDEX returns the value at the intersection of both.

Two-way lookup is especially useful when your report format matches the way people ask questions: “What were April sales for Widget A?” You are not just retrieving a record; you are retrieving a record from a matrix. INDEX MATCH handles that matrix without requiring helper macros or manual rearranging.

If you want to see how this compares with the modern alternative, XLOOKUP can simplify some lookup cases, but it does not fully replace every matrix-style formula. That is why the old pattern still shows up in reporting models, dashboards, and template workbooks.

How to use INDEX MATCH with multiple criteria

Excel allows up to 255 arguments in a function, but a readable multi-criteria lookup rarely benefits from approaching that limit (Microsoft Support). For an INDEX MATCH multiple criteria formula, use exact MATCH mode 0, then combine the required fields with a visible helper key or carefully reviewed array logic so every criterion must agree.

The simplest route is a helper column. Build a combined key in a new column, then match against that key:

=TEXT(A2,"@")&"|"&TEXT(B2,"@")

If A is product and B is region, the helper column might create values like Widget A|West. Then your lookup becomes:

=INDEX($D$2:$D$100,MATCH(G2&"|"&G3,$C$2:$C$100,0))

That approach is easy to audit because the criteria are visible in the sheet. It also scales nicely when non-technical teammates will inherit the workbook later.

If you do not want a helper column, you can also build the criteria into the lookup itself with array logic. That version is more advanced and harder to read, so I usually recommend helper columns first unless the workbook design makes them impossible.

A good rule of thumb is this: if multiple people will edit the file, optimize for clarity. If only you will maintain it, optimize for whatever keeps the formula shortest without sacrificing accuracy. In both cases, test the formula on a row you already know is correct before copying it downward.

Common INDEX MATCH problems and when to switch to XLOOKUP

Excel permits 8,192 characters in a formula and 64 nested function levels, but longer is not safer (Microsoft Support). Most INDEX MATCH failures come from 5 simpler causes: unequal range sizes, extra spaces, text-number mismatches, duplicate keys, or the wrong MATCH mode. Test those first.

The fastest debug path is simple:

1. Test MATCH by itself and confirm the row number.
2. Confirm the lookup range and return range are the same size.
3. Check for hidden spaces with TRIM if text should match but does not.
4. Verify numbers are really numbers, not text that only looks numeric.
5. If duplicates exist, decide which one should win before the formula runs.

Example fixes:

=INDEX($C$2:$C$6,MATCH(TRIM(F2),$A$2:$A$6,0))

=INDEX($C$2:$C$6,MATCH(VALUE(F2),$A$2:$A$6,0))

If your workbook supports Microsoft 365, XLOOKUP is often the cleaner choice. It removes the need to combine two functions just to get a basic return value, and it lets you define a friendly not-found result directly in the formula. That makes it easier to read, easier to teach, and easier to audit.

Still, INDEX MATCH remains a good skill to keep. It works in older files, it handles left lookup naturally, and it is still a common pattern in shared corporate workbooks. If you understand both, you can work comfortably across older models and newer templates.

Practical tips for using INDEX MATCH in real workbooks

Excel worksheets allow 1,048,576 rows and 16,384 columns, so a small reference mistake can hide inside a large model for a long time (Microsoft Support). Make the formula auditable before making it clever: use aligned ranges, stable keys, clear names, and known-answer tests.

A few habits help immediately:

• Give the lookup range and result range clear names when the workbook is shared.
• Keep row and column ranges the same size so the position number always maps cleanly.
• Use MATCH alone first, because a wrong position number is easier to spot than a nested formula error.
• Prefer helper columns for multi-criteria lookups when the file will be maintained by other people.
• Use TRIM, VALUE, or TEXT only when you have confirmed that the source data is inconsistent.

If you are building a report for a team, readability usually matters more than shaving off a few characters. A named range like SalesRegion is slower to type than $A$2:$A$100, but it is much easier to understand six months later. The same is true for helper columns: they make the workbook a little wider, but they usually make the logic much more durable.

You should also think about what happens when the table grows. Because INDEX MATCH references positions rather than hard-coded return columns, it is naturally more resilient than VLOOKUP when columns are inserted. That does not make it maintenance-free, though. If the lookup key changes format, or if duplicates appear in the source data, the formula can still return the wrong answer with no visible warning.

That is why the best production workflow is simple: test the formula on a known good row, copy it only after the position output is correct, and keep an eye on the data hygiene around the lookup column. Once those basics are in place, INDEX MATCH becomes a dependable building block instead of a fragile workaround.

FAQ

INDEX MATCH is a 2-function lookup pattern, and Microsoft documents 3 MATCH modes: -1, 0, and 1 (Microsoft Support). The answers below cover exact-position lookup, left lookup, multiple criteria, and the practical point at which XLOOKUP becomes the cleaner choice.

What does INDEX MATCH do in Excel?

It finds a position with MATCH and returns the value at that position with INDEX. Use it when you need a flexible lookup that can return values from any column or row in a range.

Is INDEX MATCH better than VLOOKUP?

Usually, yes. INDEX MATCH is more flexible because it does not depend on hard-coded column numbers and it can look left as easily as right. VLOOKUP is still fine for simple legacy files.

Can INDEX MATCH look left?

Yes. That is one of its biggest strengths. Since INDEX returns from a separate result range, the return column does not have to sit to the right of the lookup column.

Why use XLOOKUP instead?

Use XLOOKUP when your Excel version supports it and you want the cleanest modern lookup formula. It is easier to read, handles not-found output inside the formula, and covers many cases that previously needed INDEX MATCH.

Does INDEX MATCH work with multiple criteria?

Yes. The easiest approach is a helper column that combines the criteria into one lookup key. You can also use more advanced array-style formulas, but helper columns are usually easier to maintain.

Final takeaway

Microsoft documents 2 INDEX forms and 3 MATCH modes, but the dependable exact-lookup recipe needs only the array form plus mode 0 (INDEX, MATCH). That combination supports left lookup, two-way lookup, and multiple criteria without forcing you to reorganize the source table.

For a broader lookup strategy, compare it with Every Excel LOOKUP Explained, then decide whether XLOOKUP or INDEX MATCH is the better fit for your workbook. When you understand both, you can choose the simplest formula that still survives real-world spreadsheet changes. That choice also gives the next workbook owner a clearer formula to audit and maintain.