Mastering INDEX and MATCH for Accounting

While VLOOKUP is a common starting point, professional accountants prefer the INDEX and MATCH combination. It is more flexible, less prone to breaking when columns are moved, and capable of looking up data to the left.

The Syntax

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

A Real-World Example

Imagine you need to find an account balance based on an Account ID from your Trial Balance:

Account IDAccount NameBalance
1001Cash10000
1002Inventory5000

To find the balance for Account 1002:

=INDEX(C2:C3, MATCH(1002, A2:A3, 0))

MATCH returns 2 (because 1002 is the second row). INDEX looks at the second row of column C and returns 5000.

Why use INDEX/MATCH over VLOOKUP?

INDEX and MATCH allow you to rearrange your spreadsheet columns without breaking your formulas. VLOOKUP requires your lookup value to be in the first column of your selection, whereas INDEX/MATCH does not.

Common Mistakes

Ready to try it? Practice advanced lookups here.