INDEX MATCH in Excel for Financial Models

INDEX MATCH is a two-function lookup combo that returns a value from a range based on a matched position. INDEX returns the cell at a given row and column position, and MATCH finds that position by searching for a value. In financial models it is the workhorse for pulling assumptions, mapping accounts, and building lookups that do not break when you insert or move columns, which is exactly where VLOOKUP fails.

Syntax and how the two functions combine

INDEX takes a range and a position: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). MATCH returns the relative position of lookup_value inside lookup_range, and INDEX uses that position to pull the matching value from return_range.

The third argument of MATCH controls match type. Use 0 for an exact match (the default for almost all model lookups), 1 for the largest value less than or equal to the lookup value (requires ascending sort), and -1 for the smallest value greater than or equal to it (requires descending sort).

Because the lookup column and the return column are specified independently, you can look left or right with equal ease. VLOOKUP can only return columns to the right of the lookup column.

Worked example: pulling a tax rate by year

Suppose your assumptions block lists fiscal years in B2:B6 and effective tax rates in C2:C6. You want to pull the rate for the year sitting in cell F1.

1. In a cell, enter =INDEX(C2:C6, MATCH(F1, B2:B6, 0)).
2. MATCH(F1, B2:B6, 0) finds the row position of the year in F1 within B2:B6. If F1 holds 2027 and 2027 is the third year listed, MATCH returns 3.
3. INDEX(C2:C6, 3) then returns the third rate in C2:C6, say 0.21.
4. Drag the formula across forecast columns and the lookup self-adjusts as F1 changes per period.

MATCH(2027, B2:B6, 0) returns 3, so INDEX pulls the third rate.

Why analysts prefer it over VLOOKUP

INDEX MATCH does not hardcode a column offset. A VLOOKUP written as =VLOOKUP(F1, B2:D6, 3, 0) silently breaks the moment someone inserts a column inside B:D, because the literal 3 no longer points at the column you meant. INDEX MATCH references the return column directly, so inserting columns shifts the reference with it.

• Look in any direction: the return column can sit left of the lookup column.
• Faster on large sheets: only two columns are read, not the full table block.
• Robust to layout changes: no hardcoded column index to fall out of sync.
• Two-way lookups: nest a second MATCH in the column argument, =INDEX(data, MATCH(rowkey,...), MATCH(colkey,...)).

Common modeling pitfalls

Always pass 0 as the third MATCH argument for label and account lookups. Omitting it defaults to approximate match (type 1), which silently returns the wrong row when your lookup range is not sorted ascending.

Keep the INDEX return_range and the MATCH lookup_range the same height. If C2:C6 is five rows but B2:B7 is six, a valid MATCH position can land outside the return range and produce a wrong value or a #REF! error.

When a lookup value may be missing, wrap the combo in IFERROR so a broken mapping shows a flag instead of #N/A cascading through the model: =IFERROR(INDEX(C2:C6, MATCH(F1, B2:B6, 0)), "check").