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))
- MATCH: Finds the row position of your lookup value.
- INDEX: Returns the value at that row from your target range.
A Real-World Example
Imagine you need to find an account balance based on an Account ID from your Trial Balance:
| Account ID | Account Name | Balance |
|---|---|---|
| 1001 | Cash | 10000 |
| 1002 | Inventory | 5000 |
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
- Match Type: Always use
0as the third argument inMATCHto ensure an exact match. - Mismatched Ranges: Ensure your
INDEXrange andMATCHrange correspond to the same number of rows. - Data Types: If your lookup value is stored as text (e.g., "1002") but your data range has it as a number (1002), the match will fail.