Combination

Find the customer with a regex lookup

Pull the invoice number out of a bank description with REGEXEXTRACT, turn it into a pattern, and let XLOOKUP match that pattern against an invoice list that is typed in different ways.

Functions used:XLOOKUP REGEXEXTRACT REGEXTESTExcel for Microsoft 365 (This combination needs Excel for Microsoft 365)

Difficulty rating

Worked example

Customer behind a bank receipt with a messy reference

Scenario

For the receivables work at Veldhuis Installatie BV you match bank receipts to the receivables list. The customers write the invoice number in their own way in the description (INV-250412 or inv 25-0412), and the client's list holds the numbers in different layouts as well: INV-25-0412, inv 25-0415, INV-250420. An exact lookup on a cleaned key would first need a lot of cleaning. You want the customer next to the receipt in one formula.

The data

Sheet excerpt: F3 holds the formula
Row	A	B	C	E	F
1	Invoice no.	Customer	Open amount	Bank text	SEPA OVERBOEKING inv 25-0420 BETALING DEBITEUR
2	INV-25-0412	Mulder Bouw BV	€12,100.00€ 12.100,00
3	inv 25-0415	De Vries Transport	€8,470.00€ 8.470,00	Customer	Jansen Installatie=IF(REGEXTEST(F1, "(?i)inv[- ]?\d{2}-?\d{4}"), LET(key, REGEXEXTRACT(F1, "(?i)inv[- ]?(\d{2}-?\d{4})", 2), XLOOKUP("(?i)^inv[- ]?"&LEFT(key, 2)&"-?"&RIGHT(key, 4)&"$", A2:A7, B2:B7, "Not in receivables", 3)), "No invoice number in text")=IF(REGEXTEST(F1; "(?i)inv[- ]?\d{2}-?\d{4}"); LET(key; REGEXEXTRACT(F1; "(?i)inv[- ]?(\d{2}-?\d{4})"; 2); XLOOKUP("(?i)^inv[- ]?"&LEFT(key; 2)&"-?"&RIGHT(key; 4)&"$"; A2:A7; B2:B7; "Not in receivables"; 3)); "No invoice number in text")
4	INV-250420	Jansen Installatie	€3,025.00€ 3.025,00
5	INV-25-0433	Bakker Groothandel	€15,730.00€ 15.730,00
6	INV 25-0441	Smit Verhuur	€6,050.00€ 6.050,00
7	INV-25-0447	Van Dijk Schilderwerk	€1,210.00€ 1.210,00
8

Formula cell (F3)Input

Formula in F3

=IF(REGEXTEST(F1, "(?i)inv[- ]?\d{2}-?\d{4}"), LET(key, REGEXEXTRACT(F1, "(?i)inv[- ]?(\d{2}-?\d{4})", 2), XLOOKUP("(?i)^inv[- ]?"&LEFT(key, 2)&"-?"&RIGHT(key, 4)&"$", A2:A7, B2:B7, "Not in receivables", 3)), "No invoice number in text")

=IF(REGEXTEST(F1; "(?i)inv[- ]?\d{2}-?\d{4}"); LET(key; REGEXEXTRACT(F1; "(?i)inv[- ]?(\d{2}-?\d{4})"; 2); XLOOKUP("(?i)^inv[- ]?"&LEFT(key; 2)&"-?"&RIGHT(key; 4)&"$"; A2:A7; B2:B7; "Not in receivables"; 3)); "No invoice number in text")

Result: Jansen Installatie

How the pieces fit together

Watch out: Match mode 3 reads the lookup value as a pattern and is case sensitive, so start the pattern with (?i) and close it with $, or the lookup finds nothing or the wrong invoice.

The formula taken apart, from the inside out. Each part can be typed into its own cell to see what it returns.

```
=REGEXTEST(F1, "(?i)inv[- ]?\d{2}-?\d{4}")
```
```
=REGEXTEST(F1; "(?i)inv[- ]?\d{2}-?\d{4}")
```
Tests whether the description holds an invoice number at all: inv, then an optional dash or space, two digits, an optional dash and four digits. REGEXTEST looks for the pattern anywhere in the text, and (?i) ignores case. The description SEPA OVERBOEKING inv 25-0420 BETALING DEBITEUR contains inv 25-0420, so the result is TRUE.
```
=REGEXEXTRACT(F1, "(?i)inv[- ]?(\d{2}-?\d{4})", 2)
```
```
=REGEXEXTRACT(F1; "(?i)inv[- ]?(\d{2}-?\d{4})"; 2)
```
Return mode 2 returns the capture group, the part between the parentheses, and leaves out inv and the space. The result is the text 25-0420. Had the bank written INV-250420, the group would be 250420: the dash in the middle is optional, so both layouts are accepted.
```
="(?i)^inv[- ]?"&LEFT(REGEXEXTRACT(F1, "(?i)inv[- ]?(\d{2}-?\d{4})", 2), 2)&"-?"&RIGHT(REGEXEXTRACT(F1, "(?i)inv[- ]?(\d{2}-?\d{4})", 2), 4)&"$"
```
```
="(?i)^inv[- ]?"&LEFT(REGEXEXTRACT(F1; "(?i)inv[- ]?(\d{2}-?\d{4})"; 2); 2)&"-?"&RIGHT(REGEXEXTRACT(F1; "(?i)inv[- ]?(\d{2}-?\d{4})"; 2); 4)&"$"
```
Builds the pattern from the extracted number. LEFT takes the first two characters (25), RIGHT the last four (0420), so the dash or its absence in the bank text no longer matters. The result is the text (?i)^inv[- ]?25-?0420$: from the start of the cell, inv, an optional dash or space, 25, an optional dash, 0420, and then the end of the cell.
```
=REGEXTEST(A2:A7, "(?i)^inv[- ]?25-?0420$")
```
```
=REGEXTEST(A2:A7; "(?i)^inv[- ]?25-?0420$")
```
Applies that pattern to the six invoice numbers in A2:A7. The result is one column: FALSE, FALSE, TRUE, FALSE, FALSE, FALSE. Only INV-250420 in the third row fits: INV, a dash, 25, no dash, 0420. The other five end in a different four-digit number.
```
=IF(REGEXTEST(F1, "(?i)inv[- ]?\d{2}-?\d{4}"), LET(key, REGEXEXTRACT(F1, "(?i)inv[- ]?(\d{2}-?\d{4})", 2), XLOOKUP("(?i)^inv[- ]?"&LEFT(key, 2)&"-?"&RIGHT(key, 4)&"$", A2:A7, B2:B7, "Not in receivables", 3)), "No invoice number in text")
```
```
=IF(REGEXTEST(F1; "(?i)inv[- ]?\d{2}-?\d{4}"); LET(key; REGEXEXTRACT(F1; "(?i)inv[- ]?(\d{2}-?\d{4})"; 2); XLOOKUP("(?i)^inv[- ]?"&LEFT(key; 2)&"-?"&RIGHT(key; 4)&"$"; A2:A7; B2:B7; "Not in receivables"; 3)); "No invoice number in text")
```
The whole formula. IF first checks that the description has an invoice number. LET extracts it once and names it key. XLOOKUP, with match mode 3, takes the pattern as lookup value, finds the match in row 3 of A2:A7 and returns Jansen Installatie from B2:B7. A description without a number (a salary payment, say) shows the second message instead of #N/A.

Step by step

REGEXTEST(F1, ...) looks anywhere in SEPA OVERBOEKING inv 25-0420 BETALING DEBITEUR for inv, an optional dash or space, two digits, an optional dash and four digits. It finds inv 25-0420, so the test is TRUE and the IF continues with the lookup.
REGEXEXTRACT(F1, ..., 2) returns the capture group of that first match: the part between the parentheses, 25-0420. LET stores it as key.
LEFT(key, 2) = 25 and RIGHT(key, 4) = 0420. Joined with the fixed parts the pattern becomes (?i)^inv[- ]?25-?0420$.
XLOOKUP with match mode 3 tests every cell of A2:A7 against that pattern, from the top. INV-25-0412: after 25- comes 0412, not 0420, no match. inv 25-0415: 0415, no match.
INV-250420, the third cell: INV matches inv through (?i), the dash matches [- ]?, 25 matches, the optional dash is absent, 0420 matches and $ finds the end of the cell. A match in position 3.
Position 3 of B2:B7 (Mulder Bouw BV, De Vries Transport, Jansen Installatie) is Jansen Installatie, the result. Open amount in the same row: 3,0253.025.

Why it works

A regular expression describes the shape of an invoice number instead of one exact text, so INV-250420 in the list and inv 25-0420 in the bank text are both recognized as the same invoice. REGEXEXTRACT isolates the digits, LEFT and RIGHT rebuild them into a pattern with optional separators, and XLOOKUP in match mode 3 uses that pattern instead of an exact value. The anchors ^ and $ make the whole cell match, not only a piece of it. These are Microsoft 365 functions.

Watch out for

Match mode 3 is case sensitive. Without (?i) the pattern ^inv[- ]?25-?0420$ matches none of the six cells, because the pattern has lowercase inv while the cells with the right number start with capital INV, and you see the not-found text. Start the pattern with (?i).
Leave out ^ and $ and the pattern matches anywhere in a cell. A shorter pattern such as 25-?04 would find INV-25-0412 in the first row before INV-250420, and the lookup returns the first fit: the wrong customer. Keep both anchors in the pattern you build.
Match mode 3 and the REGEX functions only exist in Microsoft 365. In Excel 2021 or 2024 the formula returns #VALUE!. A description without an invoice number would make REGEXEXTRACT return #N/A, which is why the IF with REGEXTEST comes first.

Variations

=LET(key, REGEXEXTRACT(F1, "(?i)inv[- ]?(\d{2}-?\d{4})", 2), XLOOKUP(TRUE, REGEXTEST(A2:A7, "^inv[- ]?"&LEFT(key, 2)&"-?"&RIGHT(key, 4)&"$", 1), B2:B7, "Not in receivables"))

=LET(key; REGEXEXTRACT(F1; "(?i)inv[- ]?(\d{2}-?\d{4})"; 2); XLOOKUP(TRUE; REGEXTEST(A2:A7; "^inv[- ]?"&LEFT(key; 2)&"-?"&RIGHT(key; 4)&"$"; 1); B2:B7; "Not in receivables"))

The same lookup without match mode 3: REGEXTEST tests every cell and gives FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, and XLOOKUP looks for the first TRUE (position 3). Case is handled by the last argument 1 instead of (?i). Use it where you also want to combine the test with other conditions. It still needs Microsoft 365, because REGEXTEST does.

=LET(key, REGEXEXTRACT(F1, "(?i)inv[- ]?(\d{2}-?\d{4})", 2), FILTER(A2:C7, REGEXTEST(A2:A7, "^inv[- ]?"&LEFT(key, 2)&"-?04[2-4]\d$", 1), "Not in receivables"))

=LET(key; REGEXEXTRACT(F1; "(?i)inv[- ]?(\d{2}-?\d{4})"; 2); FILTER(A2:C7; REGEXTEST(A2:A7; "^inv[- ]?"&LEFT(key; 2)&"-?04[2-4]\d$"; 1); "Not in receivables"))

Returns every match instead of the first: the pattern ^inv[- ]?25-?04[2-4]\d$ means the series 25-0420 to 25-0449. Four cells fit (INV-250420, INV-25-0433, INV 25-0441, INV-25-0447), so FILTER returns four rows with invoice, customer and open amount. Row 1 and 2 (0412, 0415) fall outside the series.

All combinations