Combination
Find the customer from a bank description
Cut the invoice number out of a bank statement description with TEXTAFTER and TEXTBEFORE, then look up the customer in the receivables list with XLOOKUP.
Functions used:TEXTAFTERTEXTBEFOREXLOOKUPExcel 2024 or later (This combination needs Excel 2024 or later)
Difficulty rating 3 of 5
Worked example
Customer behind a bank receipt
Scenario
For the receivables work at Veldhuis Installatie BV you are matching bank receipts to the receivables list. Each description holds the invoice number after the label REF:. You want the customer name next to the receipt, without retyping the number, and a clear message when the invoice is not on the list.
The data
| Row | A | B | C | D | E | F | G | H | I |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Invoice no. | Customer | Open amount | Bank description | SEPA OVERBOEKING REF:25-0433 BETALING DEBITEUR | ||||
| 2 | 25-0412 | Mulder Bouw BV | €12,100.00€ 12.100,00 | ||||||
| 3 | 25-0415 | De Vries Transport | €8,470.00€ 8.470,00 | Customer | Bakker Groothandel=XLOOKUP(TEXTBEFORE(TEXTAFTER(H1, "REF:"), " ", 1, 0, 1), A2:A7, B2:B7, "Not in receivables", 0)=XLOOKUP(TEXTBEFORE(TEXTAFTER(H1; "REF:"); " "; 1; 0; 1); A2:A7; B2:B7; "Not in receivables"; 0) | ||||
| 4 | 25-0420 | Jansen Installatie | €3,025.00€ 3.025,00 | ||||||
| 5 | 25-0433 | Bakker Groothandel | €15,730.00€ 15.730,00 | ||||||
| 6 | 25-0441 | Smit Verhuur | €6,050.00€ 6.050,00 | ||||||
| 7 | 25-0447 | Van Dijk Schilderwerk | €1,210.00€ 1.210,00 | ||||||
| 8 |
Swipe sideways to see all columns
Formula in H3
=XLOOKUP(TEXTBEFORE(TEXTAFTER(H1,"REF:"), " ", 1, 0, 1), A2:A7, B2:B7, "Not in receivables", 0)
=XLOOKUP(TEXTBEFORE(TEXTAFTER(H1;"REF:"); " "; 1; 0; 1); A2:A7; B2:B7; "Not in receivables"; 0)
XLOOKUP X.ZOEKENFinds a value and returns the matching item from another range.Read more about XLOOKUPPress Escape to close
TEXTBEFORE TEKST.VOORThe part of a text before a delimiter.Used here as: lookup_valueThe value you are looking for.Read more about TEXTBEFOREPress Escape to close
TEXTAFTER TEKST.NAThe part of a text after a delimiter.Used here as: textThe text to cut, as a cell, a text or a range of cells.Read more about TEXTAFTERPress Escape to close
textThe text to search in.Input cell: SEPA OVERBOEKING REF:25-0433 BETALING DEBITEURPress Escape to close
delimiterThe character or text that marks where to cut.Press Escape to close
delimiterThe character or text that marks where to cut.Press Escape to close
instance_num (optional)Which occurrence of the delimiter to cut at; negative counts from the end.Press Escape to close
match_mode (optional)0 is case-sensitive (default), 1 ignores case.0: case-sensitive (default)Press Escape to close
match_end (optional)1 treats the end of the text as a delimiter.1: treat the end of the text as a delimiterPress Escape to close
lookup_arrayThe one column or row to search in.Invoice no. column, 6 cellsPress Escape to close
return_arrayThe range the answer comes from.Customer column, 6 cellsPress Escape to close
if_not_found (optional)What to show when nothing matches.Press Escape to close
match_mode (optional)Exact, next smaller, next larger, wildcard or regex.0: exact match (default)Press Escape to close
Result: Bakker Groothandel
How the pieces fit together
The formula taken apart, from the inside out. Each part can be typed into its own cell to see what it returns.
=TEXTAFTER(H1,
"REF:") =TEXTAFTER(H1;
"REF:") TEXTAFTER TEKST.NAThe part of a text after a delimiter.Read more about TEXTAFTERPress Escape to closetextThe text to search in.Input cell: SEPA OVERBOEKING REF:25-0433 BETALING DEBITEURPress Escape to closedelimiterThe character or text that marks where to cut.Press Escape to closeReturns everything after the label
REF:in the description. The result is the text25-0433 BETALING DEBITEUR: the invoice number is still followed by the rest of the description.=TEXTBEFORE(TEXTAFTER(H1,
"REF:"), " ", 1, 0, 1) =TEXTBEFORE(TEXTAFTER(H1;
"REF:"); " "; 1; 0; 1) TEXTBEFORE TEKST.VOORThe part of a text before a delimiter.Read more about TEXTBEFOREPress Escape to closeTEXTAFTER TEKST.NAThe part of a text after a delimiter.Used here as: textThe text to cut, as a cell, a text or a range of cells.Read more about TEXTAFTERPress Escape to closetextThe text to search in.Input cell: SEPA OVERBOEKING REF:25-0433 BETALING DEBITEURPress Escape to closedelimiterThe character or text that marks where to cut.Press Escape to closedelimiterThe character or text that marks where to cut.Press Escape to closeinstance_num (optional)Which occurrence of the delimiter to cut at; negative counts from the end.Press Escape to closematch_mode (optional)0 is case-sensitive (default), 1 ignores case.0: case-sensitive (default)Press Escape to closematch_end (optional)1 treats the end of the text as a delimiter.1: treat the end of the text as a delimiterPress Escape to closeTEXTBEFORE keeps the part before the first space, so only
25-0433remains. The last argument, 1, lets the end of the text count as a delimiter, so an invoice number at the very end of a description is also returned.=TEXTBEFORE(TEXTAFTER(H1,
"REF:"), " ", 1, 0, 1)=A2:A7 =TEXTBEFORE(TEXTAFTER(H1;
"REF:"); " "; 1; 0; 1)=A2:A7 TEXTBEFORE TEKST.VOORThe part of a text before a delimiter.Read more about TEXTBEFOREPress Escape to closeTEXTAFTER TEKST.NAThe part of a text after a delimiter.Used here as: textThe text to cut, as a cell, a text or a range of cells.Read more about TEXTAFTERPress Escape to closetextThe text to search in.Input cell: SEPA OVERBOEKING REF:25-0433 BETALING DEBITEURPress Escape to closedelimiterThe character or text that marks where to cut.Press Escape to closedelimiterThe character or text that marks where to cut.Press Escape to closeinstance_num (optional)Which occurrence of the delimiter to cut at; negative counts from the end.Press Escape to closematch_mode (optional)0 is case-sensitive (default), 1 ignores case.0: case-sensitive (default)Press Escape to closematch_end (optional)1 treats the end of the text as a delimiter.1: treat the end of the text as a delimiterPress Escape to close=Equal toPress Escape to closeReferenceInvoice no. column, 6 cellsPress Escape to closeCompares the extracted number with the six invoice numbers in
A2:A7. The result is one column: FALSE, FALSE, FALSE, TRUE, FALSE, FALSE. Only the fourth invoice,25-0433, matches.=XLOOKUP(TEXTBEFORE(TEXTAFTER(H1,
"REF:"), " ", 1, 0, 1), A2:A7, B2:B7, "Not in receivables", 0) =XLOOKUP(TEXTBEFORE(TEXTAFTER(H1;
"REF:"); " "; 1; 0; 1); A2:A7; B2:B7; "Not in receivables"; 0) XLOOKUP X.ZOEKENFinds a value and returns the matching item from another range.Read more about XLOOKUPPress Escape to closeTEXTBEFORE TEKST.VOORThe part of a text before a delimiter.Used here as: lookup_valueThe value you are looking for.Read more about TEXTBEFOREPress Escape to closeTEXTAFTER TEKST.NAThe part of a text after a delimiter.Used here as: textThe text to cut, as a cell, a text or a range of cells.Read more about TEXTAFTERPress Escape to closetextThe text to search in.Input cell: SEPA OVERBOEKING REF:25-0433 BETALING DEBITEURPress Escape to closedelimiterThe character or text that marks where to cut.Press Escape to closedelimiterThe character or text that marks where to cut.Press Escape to closeinstance_num (optional)Which occurrence of the delimiter to cut at; negative counts from the end.Press Escape to closematch_mode (optional)0 is case-sensitive (default), 1 ignores case.0: case-sensitive (default)Press Escape to closematch_end (optional)1 treats the end of the text as a delimiter.1: treat the end of the text as a delimiterPress Escape to closelookup_arrayThe one column or row to search in.Invoice no. column, 6 cellsPress Escape to closereturn_arrayThe range the answer comes from.Customer column, 6 cellsPress Escape to closeif_not_found (optional)What to show when nothing matches.Press Escape to closematch_mode (optional)Exact, next smaller, next larger, wildcard or regex.0: exact match (default)Press Escape to closeXLOOKUP searches
25-0433inA2:A7, finds it in the fourth row and returns the customer from the same row ofB2:B7: Bakker Groothandel. If the number is not in the list, it shows the not-found text instead of #N/A.
Step by step
- TEXTAFTER(H1, "REF:") cuts the description at the label and keeps what follows: 25-0433 BETALING DEBITEUR.
- TEXTBEFORE(..., " ", 1, 0, 1) keeps the part before the first space:
25-0433. - XLOOKUP compares
25-0433withA2:A7:25-0412,25-0415and25-0420do not match,25-0433is the fourth value, so the match is in position 4. - Position 4 of
B2:B7(Mulder Bouw BV, De Vries Transport, Jansen Installatie, Bakker Groothandel) is Bakker Groothandel, the result.
Why it works
The label REF: is a fixed anchor, so TEXTAFTER does not need to know where in the description the number sits or how long the text before it is. TEXTBEFORE then cuts off the tail at the next space. What remains is a clean key, and XLOOKUP does an ordinary exact lookup with it.
Watch out for
- If the description has no
REF:label, TEXTAFTER returns #N/A, and XLOOKUP passes that error on. The not-found text only appears when the label is present but the number is not in the list. - By default the label is matched with capitals. A bank that writes
Ref:gives #N/A. Set the fourth argument of TEXTAFTER to 1 to ignore case. - Some banks put a character straight after the number, for example
25-0433/. The cut at the space then leaves the slash in the key and the lookup fails. Check a few descriptions of each bank before you copy the formula down.
Variations
=IFERROR(INDEX(B2:B7,MATCH(MID(H1, FIND("REF:", H1) + 4, 7), A2:A7, 0)), "Not in receivables")
=IFERROR(INDEX(B2:B7;MATCH(MID(H1; FIND("REF:"; H1) + 4; 7); A2:A7; 0)); "Not in receivables")
IFERROR ALS.FOUTReturns a fallback value when a formula produces any error.Read more about IFERRORPress Escape to close
INDEXReturns the value at a given row and column of a range.Used here as: valueThe calculation to try; its result is returned when it is not an error.Read more about INDEXPress Escape to close
arrayThe block of cells to pick from. Numbering starts at its top-left cell.Customer column, 6 cellsPress Escape to close
MATCH VERGELIJKENOlder position lookup; XMATCH replaces it.Used here as: row_numWhich row of the block, counted from 1. Use 0 for every row.Read more about MATCHPress Escape to close
MID DEELCharacters from the middle of a text, by start position and length.Used here as: lookup_valueThe value whose position you want.Read more about MIDPress Escape to close
textThe text to take a piece from; usually a cell reference.Input cell: SEPA OVERBOEKING REF:25-0433 BETALING DEBITEURPress Escape to close
FIND VIND.ALLESThe position of a text inside another, case-sensitive.Used here as: start_numPosition of the first character you want. The first character is 1.Read more about FINDPress Escape to close
find_textThe text to look for. Case counts; * and ? are ordinary characters.Press Escape to close
within_textThe text to search in; usually a cell reference.Input cell: SEPA OVERBOEKING REF:25-0433 BETALING DEBITEURPress Escape to close
+AddsPress Escape to close
NumberLiteral valuePress Escape to close
num_charsHow many characters to take. More than are left is allowed.Press Escape to close
lookup_arrayThe single row or column to search.Invoice no. column, 6 cellsPress Escape to close
match_type (optional)0 exact; 1 largest value <= (ascending data); -1 smallest value >= (descending data).0: exact match, wildcards allowed for text, any orderPress Escape to close
value_if_errorWhat to show instead when the calculation returns any error.Press Escape to close
The older way without TEXTAFTER: FIND gives the label at position 18, so MID starts at 18 + 4 = 22 and takes 7 characters (25-0433). It only works while every invoice number has exactly 7 characters, and IFERROR also hides a missing label.
=IFERROR(INDEX(B2:B7,MATCH(TRUE, ISNUMBER(SEARCH(A2:A7, H1)), 0)), "Not in receivables")
=IFERROR(INDEX(B2:B7;MATCH(TRUE; ISNUMBER(SEARCH(A2:A7; H1)); 0)); "Not in receivables")
IFERROR ALS.FOUTReturns a fallback value when a formula produces any error.Read more about IFERRORPress Escape to close
INDEXReturns the value at a given row and column of a range.Used here as: valueThe calculation to try; its result is returned when it is not an error.Read more about INDEXPress Escape to close
arrayThe block of cells to pick from. Numbering starts at its top-left cell.Customer column, 6 cellsPress Escape to close
MATCH VERGELIJKENOlder position lookup; XMATCH replaces it.Used here as: row_numWhich row of the block, counted from 1. Use 0 for every row.Read more about MATCHPress Escape to close
lookup_valueThe value whose position you want.Logical valuePress Escape to close
ISNUMBER ISGETALTRUE when a value is a number.Used here as: lookup_arrayThe single row or column to search.Read more about ISNUMBERPress Escape to close
SEARCH VIND.SPECThe position of a text inside another, case-insensitive, wildcards allowed.Used here as: valueThe value or cell to test.Read more about SEARCHPress Escape to close
find_textThe text to look for. Case is ignored; ? and * are wildcards, ~ escapes them.Invoice no. column, 6 cellsPress Escape to close
within_textThe text to search in; usually a cell reference.Input cell: SEPA OVERBOEKING REF:25-0433 BETALING DEBITEURPress Escape to close
match_type (optional)0 exact; 1 largest value <= (ascending data); -1 smallest value >= (descending data).0: exact match, wildcards allowed for text, any orderPress Escape to close
value_if_errorWhat to show instead when the calculation returns any error.Press Escape to close
Works without a label: it checks which invoice number from A2:A7 occurs anywhere in the description. Handy when the number has no fixed position, but it can match a number inside a longer one.
Related combinations
Functions used
- TEXTAFTERCuts a text at a delimiter and returns everything that comes after it, optionally after the n-th delimiter or counted from the end.
- TEXTBEFOREReturns the part of a text that sits before a chosen delimiter, counting from the start or from the end, without LEFT and FIND.
- XLOOKUPFind a value in one list and bring back the matching item from another.
Functions used
- TEXTAFTERCuts a text at a delimiter and returns everything that comes after it, optionally after the n-th delimiter or counted from the end.
- TEXTBEFOREReturns the part of a text that sits before a chosen delimiter, counting from the start or from the end, without LEFT and FIND.
- XLOOKUPFind a value in one list and bring back the matching item from another.