A lookup that silently returns the wrong row is worse than one that fails.
MATCH finds the row where a value appears; INDEX returns the value in that row from another column. Always use exact match (0 as the last argument of MATCH), wrap the result in IFNA to show missing items clearly, and check that lookup keys are unique, because MATCH only returns the first match.
MATCH returns the position of a value in a list:
=MATCH(H2,$A$2:$A$500,0)
The final 0 means exact match. Without it, MATCH assumes the list is sorted and can return a near value, which is how wrong balances end up in reconciliations.
INDEX returns the value at a position:
=INDEX($C$2:$C$500,5)
Combined, they look up any column using any other column:
=INDEX($C$2:$C$500,MATCH(H2,$A$2:$A$500,0))
Put this month's trial balance in one sheet and last month's in another, both with account code in column A and balance in column C. In the comparison sheet:
=IFNA(INDEX(Prior!$C$2:$C$500,MATCH($A2,Prior!$A$2:$A$500,0)),"Not in prior TB")
Accounts that exist this month but not last month show a clear message instead of an error or a zero. That is exactly the list a reviewer wants: new accounts are worth a question.
To pick a value from a grid by row and column, use two MATCHes:
=INDEX($B$2:$M$200,MATCH($P2,$A$2:$A$200,0),MATCH(Q$1,$B$1:$M$1,0))
This finds the account down the side and the month across the top.
MATCH returns the first match only. If an account code, supplier code or invoice number appears twice in the lookup list, the second row is ignored without any warning. Before relying on a lookup, test for duplicates in the key column:
=COUNTIF($A$2:$A$500,A2)
Anything above 1 needs fixing in the source. For reconciliations where both sides can have repeated values, match on a combined key (for example account and entity) or use the methods in reconciling transactions with XLOOKUP.
A code stored as the number 1001 does not match the text "1001". If a lookup fails on a value you can see in the list, check the format of both sides with =ISTEXT(A2). Convert one side so both match, rather than adding conversions inside every formula.
There is one place approximate match is useful: looking up a rate from a banded table, such as an approval limit band or a provision rate by age. Sort the band starts in ascending order and use 1 as the last argument of MATCH, so it returns the band the value falls into. Everywhere else, use 0.
XLOOKUP (Excel 365 and Excel 2021 or later) does the same job in one function and defaults to exact match. INDEX MATCH works in every version of Excel, which matters if you share files with people on older versions. The checks above apply to both.
The functions are the same in UK and US Excel. Some European language versions use semicolons instead of commas between arguments; Excel converts this automatically when a file is opened on a different regional setting.
Paste or upload your invoice list and see likely duplicates in seconds. Runs in your browser; nothing is uploaded.
The Balance Sheet Reconciliation Library covers the balance sheet with nine working papers: a known error blocks sign-off, every supported balance needs an evidence reference and every aged item needs an owner and a next action. Excel, no macros, one-time price.
VLOOKUP can only look to the right of the key and breaks when columns are inserted. INDEX MATCH looks in any direction and refers to columns directly.
Usually a format mismatch (text against number), extra spaces, or a lookup range that doesn't include that row.
Not directly. XLOOKUP can search from the bottom, or you can add a helper key that makes each row unique.
Last updated 04 November 2026. General guidance for finance teams; check it against your own policies, systems and local rules.