XMATCH

You received the trial balance of Veldhuis Installatie BV as a list, sorted by account number. During the reconciliation you keep asking where a given account sits in that list. You want a cell where you type an account number and see its position, without counting rows by hand.

Formula in F2

=XMATCH(E2, A2:A7)

=XMATCH(E2; A2:A7)

Result: 55

Watch out: If the export stores account numbers as text ("4500") while you type the number 4500, there is no match and you get #N/A. Check that both sides are the same type before you conclude that an account is missing.

The XAF export of Veldhuis Installatie BV has one text key per journal line, built as journal, entry number, relation and VAT rate, such as VRK|25-0412|D1001|21%. You want to know on which row the first line for a relation code appears, as a starting point for a sample.

Some keys were typed in lower case, and you copy the formula down for several relations.

Formula in E2

=XMATCH("*" & D2 & "*", $A$2:$A$7, 2)

=XMATCH("*" & D2 & "*"; $A$2:$A$7; 2)

Result: 33

Watch out: *D100* would match D1001, D1002 and D1003 alike. A pattern with asterisks on both sides matches anywhere in the key, so a short code finds the wrong relation.

=XMATCH("*|" & D2 & "|*", $A$2:$A$7, 2)

=XMATCH("*|" & D2 & "|*"; $A$2:$A$7; 2)

For the cut-off test you have the sales journal of Veldhuis Installatie BV for the last quarter of 2025, oldest line at the top. You type a customer code and want the date and invoice number of its last invoice before 31 December 2025. The same customer appears several times, so the search has to start from the bottom.

Formula in F2

=INDEX(A2:C8, XMATCH(F1, B2:B8, 0, -1), {1,3})

=INDEX(A2:C8; XMATCH(F1; B2:B8; 0; -1); {1\3})

Result: 2 Dec 2025 | 25-03772 Dec 2025 | 25-0377

Watch out: If a back-dated invoice from October is posted at the bottom of the journal, the last occurrence is no longer the latest date, and the formula returns that October invoice without any error. Keep the list sorted by date, or use MAXIFS on the dates (MAXIFS needs Excel 2019 or later).

=XLOOKUP(F1, B2:B8, C2:C8, "No invoice", 0, -1)

=XLOOKUP(F1; B2:B8; C2:C8; "No invoice"; 0; -1)

=INDEX(A2:C8, XMATCH(F1, B2:B8, 0, -1), 0)

=INDEX(A2:C8; XMATCH(F1; B2:B8; 0; -1); 0)

=XMATCH("^D100[12]$", B2:B8, 3, -1)

=XMATCH("^D100[12]$"; B2:B8; 3; -1)

During the receivables work you list the differences found per journal entry for Veldhuis Installatie BV, all as overstatements in euros. You want to know at which entry the running total first reaches performance materiality, without a helper column in the working paper. The formula must keep working when the threshold in E1 changes.

Formula in E2

=LET(
  running, SCAN(0, B2:B9, LAMBDA(total, x, total + x)),
  pos, XMATCH(E1, running, 1),
  INDEX(A2:A9, pos)
)

=LET(
  running; SCAN(0; B2:B9; LAMBDA(total; x; total + x));
  pos; XMATCH(E1; running; 1);
  INDEX(A2:A9; pos)
)

Result: 25-0287

Watch out: The mirror-image mistake is match_mode -1. It returns 35200 at position 4, entry 25-0233: the last entry before the threshold was reached. The answer is off by exactly one entry and looks entirely reasonable.

=INDEX(A2:A9, XMATCH(TRUE, SCAN(0, B2:B9, LAMBDA(total, x, total + x)) >= E1))

=INDEX(A2:A9; XMATCH(TRUE; SCAN(0; B2:B9; LAMBDA(total; x; total + x)) >= E1))