What is INDEX MATCH? Definition, how it works, and when to use it

Neither half is a lookup on its own. The technique is what happens when you nest one inside the other, and the reason people still teach it after XLOOKUP arrived is that nothing in the formula depends on a column being in a particular place.

How the two functions fit together

Exceljet reduces each function to a single job: "INDEX retrieves the value at a given location in a range", while "The MATCH function is designed for one purpose: find the position of an item in a range." Put the second inside the first and the position one function calculates becomes the coordinate the other reads.

Ablebits writes the pattern as a template: INDEX(column to return a value from, MATCH(lookup value, column to look up against, 0)). That trailing 0 is the argument people forget. It asks MATCH for an exact match; 1 or an omitted third argument asks for approximate matching, and Microsoft is explicit about the condition that comes with it — "If match_type is 1 or not specified, the values in lookup_array should be in an ascending order." On an unsorted column of IDs or names, approximate matching returns a confident wrong answer.

Here is the case VLOOKUP cannot do at all. Employee names sit in column B, departments in column C, and the employee ID — the value you have — in column D. The ID you are looking up is in G2, so the formula is =INDEX(B2:B200, MATCH(G2, D2:D200, 0)).

MATCH scans D2:D200 for the ID in G2 and returns its position within that range — 47, say. INDEX then reads the 47th cell of B2:B200 and hands back the name. The only alignment rule is that both ranges are the same height and start on the same row; get that wrong and the formula returns the wrong row rather than an error.

VLOOKUP cannot answer this without physically moving column D. Microsoft states the constraint plainly: "the VLOOKUP function can only look up a value from left to right. This means that the column containing the value you look up should always be located to the left of the column containing the return value" — and its recommendation for the case is to "Use the combination of INDEX and MATCH functions instead."

Two-way lookups

INDEX accepts a row and a column, so a second MATCH turns the same pattern into a grid lookup. Exceljet's form — =INDEX(C3:E11, MATCH(H2,B3:B11,0), MATCH(H3,C2:E2,0)) — uses "MATCH twice – once to get a row position, and once to get a column position".

One MATCH walks the row labels down the left edge, the other walks the headers along the top, and INDEX returns the cell where they cross. That is the shape for a rate card, a price matrix or a month-by-region grid.

INDEX MATCH vs VLOOKUP vs XLOOKUP

Every quote there is Ablebits', and the picture is one-sided because the comparison is: on the criteria that matter for a model somebody else will edit, the two-function version wins.

XLOOKUP changes the calculus rather than the criteria. It covers the left lookup and the column-insertion problem in one function, so where everyone is on a version that has it, nesting two functions is mostly habit — Ablebits points modern-Excel readers at the same idea in updated form, noting that "In Excel 365 and Excel 2021, you can use a more modern INDEX XMATCH formula." What INDEX MATCH still owns is the two-way lookup above, and any file that has to open on a version you do not control.

Excel and Google Sheets

Both functions exist in both applications with the same shape, so an INDEX MATCH formula pastes across unchanged. Google documents INDEX as returning "the content of a cell, specified by row and column offset", with the signature INDEX(reference, [row], [column]), and records a behaviour worth knowing in either app: "If you set row or column to 0, INDEX returns the array of values for the entire column or row, respectively" — which turns INDEX into a way of handing a whole column to another function, not just one cell.

How Formula Foundry handles this

A working INDEX MATCH is two or three function calls deep, and the formula bar shows it as one long line. Formula Foundry opens the selected cell's formula in the sidebar across multiple lines, indented along the nesting, with functions, ranges, constants and named references each in their own colour and matching parentheses highlighted. The classic bug — a return range and a lookup range that are not the same height, or do not start on the same row — is an alignment problem, and alignment is only visible when both ranges are.

For a formula you did not write, the visual builder renders the call tree as a form: every argument named, nested groups laid out as groups, with a live preview as you fill the fields. Click into a nested INDEX MATCH and MATCH's lookup value, lookup array and match type arrive as labelled inputs rather than a comma-counting exercise. The AI assistant does the same job in prose, explaining an existing formula step by step — usually the faster route when the question is simply what the thing is looking up.

The limit is worth stating. Formula Foundry does not evaluate your workbook; it parses, formats, explains and translates. It cannot tell you that MATCH will return #N/A because the ID genuinely is not in column D, or that a trailing space is stopping a match — those are facts about your data, and only the spreadsheet knows them. What it catches is the syntax layer, before the formula reaches the cell. You can paste a formula into the free playground and see the coloured, indented version in the browser, with nothing installed.

FAQ