Combination
Invoices missing from the ledger with FILTER, ISNA and XMATCH
List the invoices on the receivables specification that are not in the general ledger: XMATCH looks each one up, ISNA turns every miss into TRUE, and FILTER keeps those rows, with a message when nothing is missing.
Functions used:FILTERISNAXMATCHExcel 2021 or later (This combination needs Excel 2021 or later)
Worked example
Receivables invoices that are not in the ledger
Scenario
For the audit of Veldhuis Installatie BV you have the receivables specification at 30 September 2026 (columns A to C) and the list of sales invoice numbers you extracted from the general ledger XAF auditfile (column D).
You want to see which invoices on the specification have no posting in the ledger, together with customer and open amount, so you can ask the client for the underlying documents.
The data
| Row | A | B | C | D | E | F | G | H | I | J |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Invoice (spec.) | Customer | Open amount | Invoice (ledger) | ||||||
| 2 | 25-0412 | Hofman Techniek BV | €4,250.00€ 4.250,00 | 25-0412 | 25-0420=FILTER(A2:C8, ISNA(XMATCH(A2:A8, D2:D8)), "Nothing missing")=FILTER(A2:C8; ISNA(XMATCH(A2:A8; D2:D8)); "Nothing missing") | Jansen Logistiek BV | €12,600.00€ 12.600,00 | |||
| 3 | 25-0415 | De Bruin Metaal BV | €1,800.00€ 1.800,00 | 25-0415 | 25-0441 | Van Dijk Transport BV | €980.00€ 980,00 | |||
| 4 | 25-0420 | Jansen Logistiek BV | €12,600.00€ 12.600,00 | 25-0433 | ||||||
| 5 | 25-0433 | Kuiper Bouw BV | €3,375.00€ 3.375,00 | 25-0447 | ||||||
| 6 | 25-0441 | Van Dijk Transport BV | €980.00€ 980,00 | 25-0452 | ||||||
| 7 | 25-0447 | Smit Verpakking BV | €7,200.00€ 7.200,00 | 25-0460 | ||||||
| 8 | 25-0452 | Mulder Groep BV | €2,150.00€ 2.150,00 | 25-0461 | ||||||
| 9 |
Swipe sideways to see all columns
Formula in G2
=FILTER(A2:C8,ISNA(XMATCH(A2:A8, D2:D8)), "Nothing missing")
=FILTER(A2:C8;ISNA(XMATCH(A2:A8; D2:D8)); "Nothing missing")
Result: 25-0420 | Jansen Logistiek BV | €12,600.00 / 25-0441 | Van Dijk Transport BV | €980.0025-0420 | Jansen Logistiek BV | € 12.600,00 / 25-0441 | Van Dijk Transport BV | € 980,00
How the pieces fit together
Watch out: This only finds invoices that are on the specification but not in the ledger. An invoice that is in the ledger but not on the specification (here 25-0460 and 25-0461) stays hidden. For completeness, swap the two ranges and run the formula a second time.
The formula taken apart, from the inside out. Each part can be typed into its own cell to see what it returns.
=XMATCH(A2:A8,
D2:D8) =XMATCH(A2:A8;
D2:D8) XMATCH X.VERGELIJKENReturns the position of a value in a range.Read more about XMATCHPress Escape to closelookup_valueThe value whose position you want.Invoice (spec.) column, 7 cellsPress Escape to closelookup_arrayThe single row or column to search. Positions count from its first cell.Invoice (ledger) column, 7 cellsPress Escape to closeLooks up each of the seven invoice numbers of the specification in the ledger list. The result is one column: 1, 2, #N/A, 3, #N/A, 4, 5. The #N/A values are the invoices
25-0420and25-0441, which the ledger does not contain.=ISNA(XMATCH(A2:A8,
D2:D8)) =ISNA(XMATCH(A2:A8;
D2:D8)) ISNA ISNBTRUE when a value is #N/A.Press Escape to closeXMATCH X.VERGELIJKENReturns the position of a value in a range.Used here as: valueThe value or cell to test.Read more about XMATCHPress Escape to closelookup_valueThe value whose position you want.Invoice (spec.) column, 7 cellsPress Escape to closelookup_arrayThe single row or column to search. Positions count from its first cell.Invoice (ledger) column, 7 cellsPress Escape to closeISNA turns the positions into TRUE for every #N/A and FALSE for every position: FALSE, FALSE, TRUE, FALSE, TRUE, FALSE, FALSE. This is the include list for FILTER.
=FILTER(A2:C8,
ISNA(XMATCH(A2:A8, D2:D8))) =FILTER(A2:C8;
ISNA(XMATCH(A2:A8; D2:D8))) FILTERReturns every row of a range that meets a condition.Read more about FILTERPress Escape to closearrayThe rows you want back: one column or a whole table.7 rows by 3 columnsPress Escape to closeISNA ISNBTRUE when a value is #N/A.Used here as: includeOne TRUE/FALSE per row, as tall as array (as wide for columns): what to keep.Press Escape to closeXMATCH X.VERGELIJKENReturns the position of a value in a range.Used here as: valueThe value or cell to test.Read more about XMATCHPress Escape to closelookup_valueThe value whose position you want.Invoice (spec.) column, 7 cellsPress Escape to closelookup_arrayThe single row or column to search. Positions count from its first cell.Invoice (ledger) column, 7 cellsPress Escape to closeFILTER keeps the rows of the specification where the include list is TRUE: rows 3 and 5. You get two rows of three columns:
25-0420Jansen Logistiek 12,600 and25-0441Van Dijk Transport 980. If no row were TRUE, this version would show #CALC!.=FILTER(A2:C8,
ISNA(XMATCH(A2:A8, D2:D8)), "Nothing missing") =FILTER(A2:C8;
ISNA(XMATCH(A2:A8; D2:D8)); "Nothing missing") FILTERReturns every row of a range that meets a condition.Read more about FILTERPress Escape to closearrayThe rows you want back: one column or a whole table.7 rows by 3 columnsPress Escape to closeISNA ISNBTRUE when a value is #N/A.Used here as: includeOne TRUE/FALSE per row, as tall as array (as wide for columns): what to keep.Press Escape to closeXMATCH X.VERGELIJKENReturns the position of a value in a range.Used here as: valueThe value or cell to test.Read more about XMATCHPress Escape to closelookup_valueThe value whose position you want.Invoice (spec.) column, 7 cellsPress Escape to closelookup_arrayThe single row or column to search. Positions count from its first cell.Invoice (ledger) column, 7 cellsPress Escape to closeif_empty (optional)What to show when no row passes; without it you get #CALC!.Press Escape to closeThe third argument of FILTER is the text to show when no row qualifies. Here two rows qualify, so the result is the same two rows, 12,600 + 980 = 13,580 in total. If the ledger held all seven invoices, the cell would show the message instead of an error.
Step by step
XMATCH(A2:A8, D2:D8) looks up each specification invoice in the ledger list:
25-0412is at position 125-0415is at position 225-0420is not found (#N/A)25-0433is at position 325-0441is not found (#N/A)25-0447is at position 425-0452is at position 5
- ISNA turns that into FALSE, FALSE, TRUE, FALSE, TRUE, FALSE, FALSE: TRUE only for the two invoices that were not found.
- FILTER walks through
A2:C8with that list and keeps row 3 (25-0420, Jansen Logistiek BV, 12,60012.600) and row 5 (25-0441, Van Dijk Transport BV, 980). - Two rows qualify, so the third argument (the message) is not used. The result spills over
G2:I3. Together the two invoices are 12,60012.600 + 980 = 13,58013.580 of open receivables without a ledger posting.
Why it works
XMATCH returns a position for an invoice that exists and #N/A for one that does not, and ISNA turns exactly that difference into TRUE and FALSE.
FILTER needs one TRUE or FALSE per row of the specification, and XMATCH returns one answer for each value in A2:A8, whatever the length of the ledger list. The third argument takes care of the clean case where the ledger is complete.
Watch out for
- Invoice numbers with a trailing space or typed as a number in one list and as text in the other do not match, so XMATCH reports them as missing even though they are there. Check the first few hits by hand before you send a request to the client.
- The cells next to and below
G2must be empty. If something is in the way of the spill range, Excel shows #SPILL! instead of the list.
Variations
=FILTER(A2:C8,COUNTIF(D2:D8, A2:A8)=0, "Nothing missing")
=FILTER(A2:C8;COUNTIF(D2:D8; A2:A8)=0; "Nothing missing")
The older approach: COUNTIF counts how often each invoice occurs in the ledger list and FILTER keeps the zeros. Same two rows. It still needs FILTER, so it works in the same Excel versions.
=FILTER(A2:C8,ISNA(MATCH(A2:A8, D2:D8, 0)), "Nothing missing")
=FILTER(A2:C8;ISNA(MATCH(A2:A8; D2:D8; 0)); "Nothing missing")
MATCH with 0 as the third argument instead of XMATCH. The result is the same two rows; XMATCH is shorter because exact match is its default.
Related combinations
Functions used
- FILTERReturns every row of a range that meets your condition, as a live list that spills into the cells below and updates when the data changes.
- XMATCHFinds a value in a row or column and returns its position as a number, with options for nearest matches, wildcards, regular expressions and searching from the end.
Functions used
- FILTERReturns every row of a range that meets your condition, as a live list that spills into the cells below and updates when the data changes.
- XMATCHFinds a value in a row or column and returns its position as a number, with options for nearest matches, wildcards, regular expressions and searching from the end.