INDEX MATCH for Accountants: Why It Beats VLOOKUP and How to Use It

What Is INDEX MATCH?

INDEX MATCH is a combination of two Excel functions used together to perform lookups. INDEX returns the value at a specific position in a range: =INDEX(return_range, row_number). MATCH finds the position of a value in a range: =MATCH(lookup_value, lookup_range, 0). Combined: =INDEX(return_column, MATCH(lookup_value, lookup_column, 0)). This finds where your lookup value appears and returns the corresponding value from any column — to the left or right.

Why Finance Professionals Prefer INDEX MATCH Over VLOOKUP

VLOOKUP has three significant limitations. It can only look to the right — the return value must be in a column to the right of the lookup column. It breaks when columns are inserted, because the column number argument becomes wrong. And it is slower on large datasets. INDEX MATCH has none of these problems: the return column can be anywhere, it references columns by name so insertions do not break it, and it is faster on large data.

A Finance Reconciliation Example

You are reconciling a bank statement to the general ledger. The bank statement has transaction reference numbers in column B and amounts in column D. The GL export has reference numbers in column A and GL amounts in column C. =INDEX(GLSheet!C:C, MATCH(A2, GLSheet!A:A, 0)) pulls the GL amount for each bank transaction reference — regardless of column order on either sheet.

Two-Way Lookup

INDEX MATCH can perform two-way lookups (matching both a row and a column) by nesting two MATCH functions: =INDEX(table, MATCH(row_lookup, row_range, 0), MATCH(col_lookup, col_range, 0)). This is useful for pulling the right figure from a matrix report — for example, finding budget for a specific cost centre and month from a cost centre x month matrix.

XLOOKUP: The Modern Alternative

Excel 365 introduced XLOOKUP, which achieves the same results as INDEX MATCH with simpler syntax and additional features including looking left, returning multiple columns, and handling errors natively. For users on Excel 365, XLOOKUP is now the preferred function — but INDEX MATCH remains important for older Excel versions and remains widely used in practice.

Continue learning: explore accounting qualifications at Learnsignal.