XLOOKUP
X.ZOEKEN in Dutch Excel
Find a value in one list and bring back the matching item from another.
Difficulty rating 3 of 5. The three required arguments are simple; the three optional ones change how it searches and take practice to use with confidence.The three required arguments are simple; the three optional ones change how it searches and take practice to use with confidence.
Last updated: 23 October 2026
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Invoice | Customer | Invoice to find | Customer found |
| 2 | 1023110231 | Visser Bouw | 1023310233 | De Wit BV=XLOOKUP(C2, A2:A5, B2:B5)=XLOOKUP(C2; A2:A5; B2:B5) |
| 3 | 1023210232 | Jansen BV | ||
| 4 | 1023310233 | De Wit BV | ||
| 5 | 1023410234 | Bakker Metaal |
Swipe sideways to see all columns
Result: De Wit BV
=XLOOKUP(C2,A2:A5, B2:B5)
=XLOOKUP(C2;A2:A5; B2:B5)
XLOOKUP X.ZOEKENFinds a value and returns the matching item from another range.Press Escape to close
lookup_valueThe value you are looking for.Press Escape to close
lookup_arrayThe one column or row to search in.Press Escape to close
return_arrayThe range the answer comes from.Press Escape to close
What XLOOKUP does and when to use it
What it does: it finds a value in one column or row and returns the matching value from another, for example the customer that belongs to an invoice number.
When to use it: whenever you look something up in a table. It replaces VLOOKUP, HLOOKUP and most INDEX and MATCH combinations.
Three main gains:
- Any direction: the answer column can sit left or right of the column you search, and you point at it instead of counting columns.
- Exact by default: no FALSE to forget, and inserting a column in the table does not break the formula.
- Its own not-found text:
if_not_foundshows your message instead of #N/A, without IFERROR.
XLOOKUP or VLOOKUP?
Compare with VLOOKUP
- VLOOKUP: you count the column
=VLOOKUP(E2,
$A$2:$C$6, 3, FALSE) =VLOOKUP(E2;
$A$2:$C$6; 3; FALSE) VLOOKUP VERT.ZOEKENOlder vertical lookup; XLOOKUP replaces it.Read more about VLOOKUPPress Escape to closelookup_valueThe value to find in the first column of the table.Press Escape to closetable_arrayThe table; the lookup column must be its first column.Press Escape to closecol_index_numWhich column of the table holds the answer, counting from 1.Press Escape to closerange_lookup (optional)FALSE for an exact match; TRUE or omitted for approximate.Press Escape to close - XLOOKUP: you point at the column
=XLOOKUP(E2,
$A$2:$A$6, $C$2:$C$6) =XLOOKUP(E2;
$A$2:$A$6; $C$2:$C$6) XLOOKUP X.ZOEKENFinds a value and returns the matching item from another range.Press Escape to closelookup_valueThe value you are looking for.Press Escape to closelookup_arrayThe one column or row to search in.Press Escape to closereturn_arrayThe range the answer comes from.Press Escape to close
How the VLOOKUP arguments become XLOOKUP arguments
col_index_num(the 3) becomesreturn_array: you point at$C$2:$C$6instead of counting columns.range_lookupFALSE (exact) becomesmatch_mode0, which is the default, so you can leave it out.range_lookupTRUE (approximate) becomesmatch_mode-1, and the data need not be sorted.table_arrayshrinks tolookup_array: only the column you search.
Colleague on Excel 2019 or older
Excel 2016 and 2019 do not have XLOOKUP and show #NAME? once the file recalculates. For files that go to those versions, keep VLOOKUP or INDEX and MATCH.
XLOOKUP(lookup_value,lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
XLOOKUP(lookup_value;lookup_array; return_array; [if_not_found]; [match_mode]; [search_mode])
XLOOKUP X.ZOEKENFinds a value and returns the matching item from another range.Press Escape to close
lookup_valueThe value you are looking for.Press Escape to close
lookup_arrayThe one column or row to search in.Press Escape to close
return_arrayThe range the answer comes from.Press 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.Press Escape to close
search_mode (optional)Search direction, or binary search (halving) on sorted data.Press Escape to close
| Argument | Type | Meaning | Default |
|---|---|---|---|
lookup_value | Required | The value you are looking for. | |
lookup_array | Required | The one column or row to search in. | |
return_array | Required | The range the answer comes from. | |
if_not_found | Optional | What to show when nothing matches. | #N/A |
match_mode | Optional | Exact, next smaller, next larger, wildcard or regex. | 0 |
search_mode | Optional | Search direction, or binary search (halving) on sorted data. | 1 |
Return value:The matching value from the column you return (return_array), or your own message when nothing is found.
Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).
Worked examples
Beginner
Look up a balance in the trial balance
You tie your working papers to the client's trial balance (saldibalans) and want the year-end balance of accounts 1300, 1800 and 1100 with one formula copied to three cells.
| Row | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Account | Description | Balance 31 Dec 2025 | Account | Balance | ||
| 2 | 11001100 | Bank | €84,250.50€ 84.250,50 | 13001300 | €213,400.00€ 213.400,00=XLOOKUP(E2, $A$2:$A$6, $C$2:$C$6)=XLOOKUP(E2; $A$2:$A$6; $C$2:$C$6) | ||
| 3 | 13001300 | Trade receivables | €213,400.00€ 213.400,00 | 18001800 | -€28,450.00€ -28.450,00=XLOOKUP(E3, $A$2:$A$6, $C$2:$C$6)=XLOOKUP(E3; $A$2:$A$6; $C$2:$C$6) | ||
| 4 | 16001600 | Trade payables | -€156,900.25€ -156.900,25 | 11001100 | €84,250.50€ 84.250,50=XLOOKUP(E4, $A$2:$A$6, $C$2:$C$6)=XLOOKUP(E4; $A$2:$A$6; $C$2:$C$6) | ||
| 5 | 18001800 | VAT payable | -€28,450.00€ -28.450,00 | ||||
| 6 | 80008000 | Revenue | -€1,245,000.00€ -1.245.000,00 | ||||
| 7 |
Swipe sideways to see all columns
Formula in F2
=XLOOKUP(E2,$A$2:$A$6, $C$2:$C$6)
=XLOOKUP(E2;$A$2:$A$6; $C$2:$C$6)
XLOOKUP X.ZOEKENFinds a value and returns the matching item from another range.Press Escape to close
lookup_valueThe value you are looking for.Press Escape to close
lookup_arrayThe one column or row to search in.Press Escape to close
return_arrayThe range the answer comes from.Press Escape to close
Result: €213,400.00€ 213.400,00
Step by step
E2holds 1300. The$signs lock$A$2:$A$6and$C$2:$C$6, so the ranges stay put when you copy the formula.- Excel scans
$A$2:$A$6from the top: 1100, no; 1300, yes, in the second cell,A3. - It returns the second cell of
$C$2:$C$6, which isC3: €213,400.00€ 213.400,00 inF2. - Copy
F2down toF4. Only the lookup cell moves: 1800 returns -€28,450.00€ -28.450,00 and 1100 returns €84,250.50€ 84.250,50.
Watch out: Leave out the $ signs and the ranges slide down as you copy: F4 searches A4:A8 instead of A2:A6, which no longer holds account 1100, so it returns #N/A.
Intermediate
Missing invoice in the subledger
You test whether booked invoices appear in the debtors subledger. A missing one must say “No match” instead of #N/A, so you can check it.
| Row | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Invoice no. | Customer | Open amount | Invoice no. | Open amount | ||
| 2 | F-25-0391 | Hoekstra Bouw BV | €12,100.00€ 12.100,00 | F-25-0394 | €8,750.00€ 8.750,00=XLOOKUP(E2, $A$2:$A$6, $C$2:$C$6, "No match")=XLOOKUP(E2; $A$2:$A$6; $C$2:$C$6; "No match") | ||
| 3 | F-25-0392 | Smit Handel BV | €4,235.50€ 4.235,50 | F-25-0398 | No match=XLOOKUP(E3, $A$2:$A$6, $C$2:$C$6, "No match")=XLOOKUP(E3; $A$2:$A$6; $C$2:$C$6; "No match") | ||
| 4 | F-25-0394 | Van Dijk BV | €8,750.00€ 8.750,00 | ||||
| 5 | F-25-0395 | Hoekstra Bouw BV | €3,025.00€ 3.025,00 | ||||
| 6 | F-25-0397 | De Boer BV | €15,640.25€ 15.640,25 | ||||
| 7 |
Swipe sideways to see all columns
Formula in F2
=XLOOKUP(E2,$A$2:$A$6, $C$2:$C$6, "No match")
=XLOOKUP(E2;$A$2:$A$6; $C$2:$C$6; "No match")
XLOOKUP X.ZOEKENFinds a value and returns the matching item from another range.Press Escape to close
lookup_valueThe value you are looking for.Press Escape to close
lookup_arrayThe one column or row to search in.Press Escape to close
return_arrayThe range the answer comes from.Press Escape to close
if_not_found (optional)What to show when nothing matches.Press Escape to close
Result: €8,750.00€ 8.750,00
Step by step
E2holdsF-25-0394. Excel finds it in the third cell of$A$2:$A$6,A4.- It returns the third cell of
$C$2:$C$6: €8,750.00€ 8.750,00 inF2. - Copy
F2down.E3holdsF-25-0398, and none of the five invoice numbers matches it. - The fourth argument is filled in, so
F3shows the textNo matchinstead of #N/A.
Watch out: The message is text: SUM ignores it, but a calculation with + gives #VALUE!. If the column feeds a calculation, return 0 instead.
More on this example: why it works, 2 variations
Why it works
A real problem such as #REF! still shows, because if_not_found catches only a missing value.
Variations
=XLOOKUP(E2,$A$2:$A$6, $B$2:$C$6, "No match")
=XLOOKUP(E2;$A$2:$A$6; $B$2:$C$6; "No match")
XLOOKUP X.ZOEKENFinds a value and returns the matching item from another range.Press Escape to close
lookup_valueThe value you are looking for.Press Escape to close
lookup_arrayThe one column or row to search in.Press Escape to close
return_arrayThe range the answer comes from.Press Escape to close
if_not_found (optional)What to show when nothing matches.Press Escape to close
With a two-column return_array one formula returns the customer and the amount, in F2:G2.
=XLOOKUP(1,(B2:B6="Hoekstra Bouw BV")*(C2:C6=3025), A2:A6)
=XLOOKUP(1;(B2:B6="Hoekstra Bouw BV")*(C2:C6=3025); A2:A6)
XLOOKUP X.ZOEKENFinds a value and returns the matching item from another range.Press Escape to close
lookup_valueThe value you are looking for.Press Escape to close
lookup_arrayThe one column or row to search in.Press Escape to close
=Equal toPress Escape to close
TextPress Escape to close
*MultipliesPress Escape to close
ReferencePress Escape to close
=Equal toPress Escape to close
NumberPress Escape to close
return_arrayThe range the answer comes from.Press Escape to close
Two conditions: multiplying TRUE/FALSE results leaves a 1 only where both hold. It returns F-25-0395.
Advanced
Two-way lookup by account and period
An overview from the XAF auditfile (the standard audit export of Dutch accounting software) lists accounts down the side and periods 10 to 12 across, and one cell returns the movement for the chosen account and period.
| Row | A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|---|
| 1 | Account | P10 | P11 | P12 | Account | 45004500 | ||
| 2 | 11001100 | €48,100.00€ 48.100,00 | €61,250.00€ 61.250,00 | €70,300.00€ 70.300,00 | Period | P12 | ||
| 3 | 40004000 | €38,200.00€ 38.200,00 | €38,900.00€ 38.900,00 | €54,600.00€ 54.600,00 | Movement | €18,750.00€ 18.750,00=XLOOKUP(G1, A2:A5, XLOOKUP(G2, B1:D1, B2:D5))=XLOOKUP(G1; A2:A5; XLOOKUP(G2; B1:D1; B2:D5)) | ||
| 4 | 45004500 | €12,500.00€ 12.500,00 | €12,500.00€ 12.500,00 | €18,750.00€ 18.750,00 | ||||
| 5 | 80008000 | €91,900.00€ 91.900,00 | €88,300.00€ 88.300,00 | €98,800.00€ 98.800,00 | ||||
| 6 |
Swipe sideways to see all columns
Formula in G3
=XLOOKUP(G1,A2:A5, XLOOKUP(G2, B1:D1, B2:D5))
=XLOOKUP(G1;A2:A5; XLOOKUP(G2; B1:D1; B2:D5))
XLOOKUP X.ZOEKENFinds a value and returns the matching item from another range.Press Escape to close
lookup_valueThe value you are looking for.Press Escape to close
lookup_arrayThe one column or row to search in.Press Escape to close
XLOOKUP X.ZOEKENFinds a value and returns the matching item from another range.Press Escape to close
lookup_valueThe value you are looking for.Press Escape to close
lookup_arrayThe one column or row to search in.Press Escape to close
return_arrayThe range the answer comes from.Press Escape to close
Result: €18,750.00€ 18.750,00
Step by step
- The inner XLOOKUP runs first: it finds
G2(P12) in the header rowB1:D1, in the third cell, and returns the third column ofB2:D5:D2:D5. - That column is the return_array of the outer XLOOKUP, which finds
G1(4500) inA2:A5, in the third cell,A4. - It returns the third cell of
D2:D5, which isD4: €18,750.00€ 18.750,00. - Change
G1to 1100 andG2toP10, and the same formula returns €48,100.00€ 48.100,00.
Watch out: The period headers here are text (P10 to P12), so G2 must hold the text P12. If an export gives the periods as numbers (12), type the number 12 in G2 as well: a number never matches the same digits stored as text.
More on this example: 2 more pitfalls, 2 variations
More to watch out for
- If people type the inputs by hand, add if_not_found to the outer XLOOKUP, or a typo gives #N/A.
- match_mode 3 (the regex variation below) works only in Microsoft 365. In Excel 2021 or 2024 the same formula returns #VALUE!, so keep exact lookups in files that go to those versions.
Variations
=XLOOKUP(G2,B1:D1, XLOOKUP(G1, A2:A5, B2:D5))
=XLOOKUP(G2;B1:D1; XLOOKUP(G1; A2:A5; B2:D5))
XLOOKUP X.ZOEKENFinds a value and returns the matching item from another range.Press Escape to close
lookup_valueThe value you are looking for.Press Escape to close
lookup_arrayThe one column or row to search in.Press Escape to close
XLOOKUP X.ZOEKENFinds a value and returns the matching item from another range.Press Escape to close
lookup_valueThe value you are looking for.Press Escape to close
lookup_arrayThe one column or row to search in.Press Escape to close
return_arrayThe range the answer comes from.Press Escape to close
Nest the other way round: find the account row first, then pick the period.
=XLOOKUP("^P1[12]$",B1:D1, XLOOKUP(G1, A2:A5, B2:D5), "No period", 3, -1)
=XLOOKUP("^P1[12]$";B1:D1; XLOOKUP(G1; A2:A5; B2:D5); "No period"; 3; -1)
XLOOKUP X.ZOEKENFinds a value and returns the matching item from another range.Press Escape to close
lookup_valueThe value you are looking for.Press Escape to close
lookup_arrayThe one column or row to search in.Press Escape to close
XLOOKUP X.ZOEKENFinds a value and returns the matching item from another range.Press Escape to close
lookup_valueThe value you are looking for.Press Escape to close
lookup_arrayThe one column or row to search in.Press Escape to close
return_arrayThe range the answer comes from.Press 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.Press Escape to close
search_mode (optional)Search direction, or binary search (halving) on sorted data.Press Escape to close
Find the period by pattern instead of by name (Microsoft 365). ^P1[12]$ fits P11 and P12 and nothing else: ^ and $ pin the pattern to the whole header, and [12] is one digit, 1 or 2. search_mode -1 searches from the right, so the last fit wins: P12. For account 4500 the formula returns €18,750.00€ 18.750,00.
Master
Latest receipt of a customer by name pattern (match_mode 3)
On the bank statement of Veldhuis Installatie BV, customer Brouwer Klimaat BV appears in the free text in different spellings: "SEPA Brouwer Klimaat BV", "BROUWER KLIM. BV" and "brouwer klimaat b.v.". The statement is in date order and you need the date and amount of the latest receipt for a cut-off check. A different company, Brouwer Klimaatgroep NV, is on the same statement and must not be picked up.
| Row | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Description | Date | Amount | Name | Brouwer | ||
| 2 | SEPA Brouwer Klimaat BV | 14 Jan 2026 | €4,250.00€ 4.250,00 | Word | Klimaat | ||
| 3 | Kuipers Dakwerken BV | 21 Jan 2026 | €3,300.00€ 3.300,00 | Latest | 9 Mar 2026=LET( stem, E1, word, E2, pattern, "(?i)\b" & stem & "[ .]*" & LEFT(word, 4) & "[a-z.]*[ .]*b\.?v\b", XLOOKUP(pattern, $A$2:$A$9, $B$2:$C$9, "No receipt", 3, -1) )=LET( stem; E1; word; E2; pattern; "(?i)\b" & stem & "[ .]*" & LEFT(word; 4) & "[a-z.]*[ .]*b\.?v\b"; XLOOKUP(pattern; $A$2:$A$9; $B$2:$C$9; "No receipt"; 3; -1) ) | €5,775.00€ 5.775,00 | |
| 4 | BROUWER KLIM. BV | 6 Feb 2026 | €6,120.50€ 6.120,50 | ||||
| 5 | Willems Elektra VOF | 19 Feb 2026 | €2,480.00€ 2.480,00 | ||||
| 6 | brouwer klimaat b.v. deelbetaling | 9 Mar 2026 | €5,775.00€ 5.775,00 | ||||
| 7 | Brouwer Klimaatgroep NV | 24 Mar 2026 | €9,200.00€ 9.200,00 | ||||
| 8 | Peters Tegelwerk BV | 27 Mar 2026 | €1,945.25€ 1.945,25 | ||||
| 9 | Scholten Staalbouw BV | 30 Mar 2026 | €7,060.00€ 7.060,00 | ||||
| 10 |
Swipe sideways to see all columns
Formula in E3
=LET( stem,E1, word, E2, pattern, "(?i)\b" & stem & "[ .]*" & LEFT(word, 4) & "[a-z.]*[ .]*b\.?v\b", XLOOKUP(pattern, $A$2:$A$9, $B$2:$C$9, "No receipt", 3, -1) )
=LET( stem;E1; word; E2; pattern; "(?i)\b" & stem & "[ .]*" & LEFT(word; 4) & "[a-z.]*[ .]*b\.?v\b"; XLOOKUP(pattern; $A$2:$A$9; $B$2:$C$9; "No receipt"; 3; -1) )
LETNames intermediate values so a formula reads clearly and computes each once.Read more about LETPress Escape to close
name1The name you give the first value. Must start with a letter.Press Escape to close
name_value1The value or expression that name1 stands for.Press Escape to close
calculation_or_name2The final calculation, or the next name to define.Press Escape to close
name_value2 (optional)The value bound to the previous name.Press Escape to close
calculation_or_name3 (optional)The final calculation, or another name; pairs may continue.Press Escape to close
name_value3 (optional)The value bound to the previous name.Press Escape to close
&Joins textPress Escape to close
stemPress Escape to close
&Joins textPress Escape to close
TextPress Escape to close
&Joins textPress Escape to close
textThe text to take characters from; usually a cell reference.Press Escape to close
num_chars (optional)How many characters to take from the left. Leave it out and you get 1.Press Escape to close
&Joins textPress Escape to close
TextPress Escape to close
XLOOKUP X.ZOEKENFinds a value and returns the matching item from another range.Press Escape to close
lookup_valueThe value you are looking for.Press Escape to close
lookup_arrayThe one column or row to search in.Press Escape to close
return_arrayThe range the answer comes from.Press 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.Press Escape to close
search_mode (optional)Search direction, or binary search (halving) on sorted data.Press Escape to close
Result: 9 Mar 2026 | €5,775.009 Mar 2026 | € 5.775,00
Step by step
- LET reads pairs of a name and a value:
stembecomesBrouwer(fromE1) andwordbecomesKlimaat(fromE2). Nothing is searched yet. patternis joined from cells and fixed parts:(?i)\bBrouwer[ .]*Klim[a-z.]*[ .]*b\.?v\b.LEFT(word, 4)givesKlim, which covers bothKLIM.andklimaat.- Read the pattern from left to right.
(?i)ignores case.\bis a word boundary, soBrouwermust start a word.[ .]*is any number of spaces and dots, including none.[a-z.]*finishes the wordKlim.b\.?vis the letters b and v with an optional dot between them. The last\bmakes the v the end of a word. - match_mode 3 treats the lookup value as a regex and tests every cell of
$A$2:$A$9for a match anywhere in the text. No^or$is needed here, because the name sits inside a longer description. search_mode -1 starts at the bottom,A9, and works upward. A9(Scholten Staalbouw) andA8(Peters Tegelwerk) do not contain Brouwer.A7isBrouwer Klimaatgroep NV: Brouwer andKlimfit,aatgroepfits[a-z.]*, but then the pattern needs a b and findsNV. No match, which is how the other company stays out.A6isbrouwer klimaat b.v. deelbetaling:brouwer, a space,klimaat, a space andb.v, followed by a dot, which is a word boundary after the v. First match from the bottom:A6.- XLOOKUP returns the fifth row of
$B$2:$C$9, two columns wide:B6is 9 March 2026 andC6is €5,775.00€ 5.775,00. The result spills intoE3:F3. - The two earlier receipts also fit (
A2, €4,250.00€ 4.250,00 andA4, €6,120.50€ 6.120,50), but search_mode -1 stops at the last one. ChangeE1toPetersandE2toTegelwerkand the same formula returns 27 March 2026 and €1,945.25€ 1.945,25. A name with no receipt showsNo receipt.
Watch out: A pattern that is too loose gives a wrong answer without an error. (?i)Brouwer alone also fits A7, so the formula returns 24 March 2026 and €9,200.00€ 9.200,00 from Brouwer Klimaatgroep NV. Mode 3 tests anywhere in the cell, so the \b and the legal form carry the precision. The wildcard mode 2 with *Brouwer* makes the same mistake and cannot express the legal form at all.
More on this example: why it works, 2 more pitfalls, 3 variations
Why it works
The name is not an exact value in the statement, it is a family of spellings, and a pattern describes the family: case, optional dots and spaces, an abbreviated second word and the legal form. Building the pattern in LET from two cells makes it reusable for any customer, and keeping the legal form in the pattern is what separates Brouwer Klimaat BV from Brouwer Klimaatgroep NV. "Latest" means the last row, so the statement must be in date order; if it is not, sort it first or the formula returns the lowest row, not the newest date.
Do not use match_mode 3 when the bank file carries a reliable customer or IBAN column: an exact XLOOKUP on that key cannot misread a spelling. Mode 3 also tests every cell with the regex engine and cannot use binary search, so on a statement of 100,000100.000 lines it is slower. The alternative is a helper column that normalises the description once (for example with REGEXREPLACE) followed by an exact lookup: one extra column, but every step can be reviewed and the lookup stays fast.
More to watch out for
- Mode 3 is case sensitive unless the pattern starts with
(?i). Without it the pattern\bBrouwer[ .]*Klim[a-z.]*[ .]*b\.?v\bfinds none of the three receipts (the b ofBVandBROUWERare capitals) and the cell showsNo receipt, although the money arrived. - match_mode 3 works only in Microsoft 365. In Excel 2021 or 2024 the same formula returns #VALUE!, so use the SEARCH variation below in files that go to those versions.
Variations
=XLOOKUP("(?i)\bBrouwer[ .]*Klim[a-z.]*[ .]*b\.?v\b",$A$2:$A$9, $B$2:$C$9, "No receipt", 3)
=XLOOKUP("(?i)\bBrouwer[ .]*Klim[a-z.]*[ .]*b\.?v\b";$A$2:$A$9; $B$2:$C$9; "No receipt"; 3)
XLOOKUP X.ZOEKENFinds a value and returns the matching item from another range.Press Escape to close
lookup_valueThe value you are looking for.Press Escape to close
lookup_arrayThe one column or row to search in.Press Escape to close
return_arrayThe range the answer comes from.Press 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.Press Escape to close
Leave out search_mode (or use 1) and the first receipt wins: 14 January 2026 and €4,250.00€ 4.250,00 from A2.
=XLOOKUP(TRUE,REGEXTEST($A$2:$A$9, "\bBrouwer[ .]*Klim[a-z.]*[ .]*b\.?v\b", 1), $B$2:$C$9, "No receipt", 0, -1)
=XLOOKUP(TRUE;REGEXTEST($A$2:$A$9; "\bBrouwer[ .]*Klim[a-z.]*[ .]*b\.?v\b"; 1); $B$2:$C$9; "No receipt"; 0; -1)
XLOOKUP X.ZOEKENFinds a value and returns the matching item from another range.Press Escape to close
lookup_valueThe value you are looking for.Press Escape to close
REGEXTESTTests whether a text matches a regular expression.Read more about REGEXTESTPress Escape to close
textThe text or cell to test.Press Escape to close
patternThe regular expression to look for.Press Escape to close
case_sensitivity (optional)0 = case sensitive (default), 1 = ignore case.Press Escape to close
return_arrayThe range the answer comes from.Press 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.Press Escape to close
search_mode (optional)Search direction, or binary search (halving) on sorted data.Press Escape to close
The same result without match_mode 3: REGEXTEST (also Microsoft 365) returns TRUE or FALSE per line, with case_sensitivity 1 instead of (?i). Use it when you want to multiply in a second condition, for example (C2:C9>5000).
=XLOOKUP(1,--ISNUMBER(SEARCH("brouwer*klim*b*v", $A$2:$A$9)), $B$2:$C$9, "No receipt", 0, -1)
=XLOOKUP(1;--ISNUMBER(SEARCH("brouwer*klim*b*v"; $A$2:$A$9)); $B$2:$C$9; "No receipt"; 0; -1)
XLOOKUP X.ZOEKENFinds a value and returns the matching item from another range.Press Escape to close
lookup_valueThe value you are looking for.Press Escape to close
lookup_arrayThe one column or row to search in.Makes the value negative; two in a row turn TRUE and FALSE into 1 and 0Press Escape to close
-Makes the value negative; two in a row turn TRUE and FALSE into 1 and 0Press Escape to close
SEARCH VIND.SPECThe position of a text inside another, case-insensitive, wildcards allowed.Read more about SEARCHPress Escape to close
find_textThe text to look for. Case is ignored; ? and * are wildcards, ~ escapes them.? = any single character. * = any run of characters, including none. ~? = a literal question mark. ~* = a literal asterisk. ~~ = a literal tilde.Press Escape to close
within_textThe text to search in; usually a cell reference.Press Escape to close
return_arrayThe range the answer comes from.Press 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.Press Escape to close
search_mode (optional)Search direction, or binary search (halving) on sorted data.Press Escape to close
Fallback for Excel 2021 and 2024: SEARCH ignores case and understands *, and gives the same row here. It has no word boundary and no optional dot, so Brouwer Klimaat Beheer BV would also fit. Accept that, or add a helper column.
Mode explorer
Type a lookup value, choose where to look and what to return, pick a match mode and a search mode, and see which row XLOOKUP lands on.
Look in
Return from
Match mode
Only an exact match counts (the default)
Exact match, or else the next smaller value
Exact match, or else the next larger value
Wildcards count: * is any characters, ? is one
The lookup value is a regular expression pattern; case sensitive
Search mode
Search from the first row down (the default)
Search from the last row up, for the latest entry
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Reference | Amount | Days open | Result | |
| 2 | CN-0412 | -€1,200.00€ -1.200,00 | 120120 | INV-1003=XLOOKUP("x", $B$2:$B$8, $A$2:$A$8, "No match", 0, 1)=XLOOKUP("x"; $B$2:$B$8; $A$2:$A$8; "No match"; 0; 1) | |
| 3 | INV-1001 | €450.00€ 450,00 | 9595 | ||
| 4 | INV-1002 | €450.00€ 450,00 | 6464 | ||
| 5 | INV-1003 | €1,800.00€ 1.800,00 | 4141 | ||
| 6 | INV-1004 | €3,250.00€ 3.250,00 | 2727 | ||
| 7 | INV-1005 | €7,500.00€ 7.500,00 | 1212 | ||
| 8 | INV-1006 | €12,000.00€ 12.000,00 | 55 |
Swipe sideways to see all columns
- Formula cell
- Where the search lands
- What it returns
=XLOOKUP(1800, $B$2:$B$8, $A$2:$A$8, "No match", 0, 1)
=XLOOKUP(1800; $B$2:$B$8; $A$2:$A$8; "No match"; 0; 1)
Result: INV-1003
=XLOOKUP(1800, $B$2:$B$8, $A$2:$A$8, "No match", 0, -1)
=XLOOKUP(1800; $B$2:$B$8; $A$2:$A$8; "No match"; 0; -1)
Result: INV-1003
=XLOOKUP(1800, $B$2:$B$8, $A$2:$A$8, "No match", -1, 1)
=XLOOKUP(1800; $B$2:$B$8; $A$2:$A$8; "No match"; -1; 1)
Result: INV-1003
=XLOOKUP(1800, $B$2:$B$8, $A$2:$A$8, "No match", -1, -1)
=XLOOKUP(1800; $B$2:$B$8; $A$2:$A$8; "No match"; -1; -1)
Result: INV-1003
=XLOOKUP(1800, $B$2:$B$8, $A$2:$A$8, "No match", 1, 1)
=XLOOKUP(1800; $B$2:$B$8; $A$2:$A$8; "No match"; 1; 1)
Result: INV-1003
=XLOOKUP(1800, $B$2:$B$8, $A$2:$A$8, "No match", 1, -1)
=XLOOKUP(1800; $B$2:$B$8; $A$2:$A$8; "No match"; 1; -1)
Result: INV-1003
=XLOOKUP(1800, $B$2:$B$8, $A$2:$A$8, "No match", 2, 1)
=XLOOKUP(1800; $B$2:$B$8; $A$2:$A$8; "No match"; 2; 1)
Result: INV-1003
=XLOOKUP(1800, $B$2:$B$8, $A$2:$A$8, "No match", 2, -1)
=XLOOKUP(1800; $B$2:$B$8; $A$2:$A$8; "No match"; 2; -1)
Result: INV-1003
=XLOOKUP(1800, $B$2:$B$8, $A$2:$A$8, "No match", 3, 1)
=XLOOKUP(1800; $B$2:$B$8; $A$2:$A$8; "No match"; 3; 1)
Result: INV-1003
=XLOOKUP(1800, $B$2:$B$8, $A$2:$A$8, "No match", 3, -1)
=XLOOKUP(1800; $B$2:$B$8; $A$2:$A$8; "No match"; 3; -1)
Result: INV-1003
=XLOOKUP(1800, $B$2:$B$8, $B$2:$B$8, "No match", 0, 1)
=XLOOKUP(1800; $B$2:$B$8; $B$2:$B$8; "No match"; 0; 1)
Result: 18001800
=XLOOKUP(1800, $B$2:$B$8, $B$2:$B$8, "No match", 0, -1)
=XLOOKUP(1800; $B$2:$B$8; $B$2:$B$8; "No match"; 0; -1)
Result: 18001800
=XLOOKUP(1800, $B$2:$B$8, $B$2:$B$8, "No match", -1, 1)
=XLOOKUP(1800; $B$2:$B$8; $B$2:$B$8; "No match"; -1; 1)
Result: 18001800
=XLOOKUP(1800, $B$2:$B$8, $B$2:$B$8, "No match", -1, -1)
=XLOOKUP(1800; $B$2:$B$8; $B$2:$B$8; "No match"; -1; -1)
Result: 18001800
=XLOOKUP(1800, $B$2:$B$8, $B$2:$B$8, "No match", 1, 1)
=XLOOKUP(1800; $B$2:$B$8; $B$2:$B$8; "No match"; 1; 1)
Result: 18001800
=XLOOKUP(1800, $B$2:$B$8, $B$2:$B$8, "No match", 1, -1)
=XLOOKUP(1800; $B$2:$B$8; $B$2:$B$8; "No match"; 1; -1)
Result: 18001800
=XLOOKUP(1800, $B$2:$B$8, $B$2:$B$8, "No match", 2, 1)
=XLOOKUP(1800; $B$2:$B$8; $B$2:$B$8; "No match"; 2; 1)
Result: 18001800
=XLOOKUP(1800, $B$2:$B$8, $B$2:$B$8, "No match", 2, -1)
=XLOOKUP(1800; $B$2:$B$8; $B$2:$B$8; "No match"; 2; -1)
Result: 18001800
=XLOOKUP(1800, $B$2:$B$8, $B$2:$B$8, "No match", 3, 1)
=XLOOKUP(1800; $B$2:$B$8; $B$2:$B$8; "No match"; 3; 1)
Result: 18001800
=XLOOKUP(1800, $B$2:$B$8, $B$2:$B$8, "No match", 3, -1)
=XLOOKUP(1800; $B$2:$B$8; $B$2:$B$8; "No match"; 3; -1)
Result: 18001800
=XLOOKUP(1800, $B$2:$B$8, $C$2:$C$8, "No match", 0, 1)
=XLOOKUP(1800; $B$2:$B$8; $C$2:$C$8; "No match"; 0; 1)
Result: 4141
=XLOOKUP(1800, $B$2:$B$8, $C$2:$C$8, "No match", 0, -1)
=XLOOKUP(1800; $B$2:$B$8; $C$2:$C$8; "No match"; 0; -1)
Result: 4141
=XLOOKUP(1800, $B$2:$B$8, $C$2:$C$8, "No match", -1, 1)
=XLOOKUP(1800; $B$2:$B$8; $C$2:$C$8; "No match"; -1; 1)
Result: 4141
=XLOOKUP(1800, $B$2:$B$8, $C$2:$C$8, "No match", -1, -1)
=XLOOKUP(1800; $B$2:$B$8; $C$2:$C$8; "No match"; -1; -1)
Result: 4141
=XLOOKUP(1800, $B$2:$B$8, $C$2:$C$8, "No match", 1, 1)
=XLOOKUP(1800; $B$2:$B$8; $C$2:$C$8; "No match"; 1; 1)
Result: 4141
=XLOOKUP(1800, $B$2:$B$8, $C$2:$C$8, "No match", 1, -1)
=XLOOKUP(1800; $B$2:$B$8; $C$2:$C$8; "No match"; 1; -1)
Result: 4141
=XLOOKUP(1800, $B$2:$B$8, $C$2:$C$8, "No match", 2, 1)
=XLOOKUP(1800; $B$2:$B$8; $C$2:$C$8; "No match"; 2; 1)
Result: 4141
=XLOOKUP(1800, $B$2:$B$8, $C$2:$C$8, "No match", 2, -1)
=XLOOKUP(1800; $B$2:$B$8; $C$2:$C$8; "No match"; 2; -1)
Result: 4141
=XLOOKUP(1800, $B$2:$B$8, $C$2:$C$8, "No match", 3, 1)
=XLOOKUP(1800; $B$2:$B$8; $C$2:$C$8; "No match"; 3; 1)
Result: 4141
=XLOOKUP(1800, $B$2:$B$8, $C$2:$C$8, "No match", 3, -1)
=XLOOKUP(1800; $B$2:$B$8; $C$2:$C$8; "No match"; 3; -1)
Result: 4141
=XLOOKUP(1800, $A$2:$A$8, $A$2:$A$8, "No match", 0, 1)
=XLOOKUP(1800; $A$2:$A$8; $A$2:$A$8; "No match"; 0; 1)
Result: No match
=XLOOKUP(1800, $A$2:$A$8, $A$2:$A$8, "No match", 0, -1)
=XLOOKUP(1800; $A$2:$A$8; $A$2:$A$8; "No match"; 0; -1)
Result: No match
=XLOOKUP(1800, $A$2:$A$8, $A$2:$A$8, "No match", -1, 1)
=XLOOKUP(1800; $A$2:$A$8; $A$2:$A$8; "No match"; -1; 1)
Result: No match
=XLOOKUP(1800, $A$2:$A$8, $A$2:$A$8, "No match", -1, -1)
=XLOOKUP(1800; $A$2:$A$8; $A$2:$A$8; "No match"; -1; -1)
Result: No match
=XLOOKUP(1800, $A$2:$A$8, $A$2:$A$8, "No match", 1, 1)
=XLOOKUP(1800; $A$2:$A$8; $A$2:$A$8; "No match"; 1; 1)
Result: No match
=XLOOKUP(1800, $A$2:$A$8, $A$2:$A$8, "No match", 1, -1)
=XLOOKUP(1800; $A$2:$A$8; $A$2:$A$8; "No match"; 1; -1)
Result: No match
=XLOOKUP(1800, $A$2:$A$8, $A$2:$A$8, "No match", 2, 1)
=XLOOKUP(1800; $A$2:$A$8; $A$2:$A$8; "No match"; 2; 1)
Result: No match
=XLOOKUP(1800, $A$2:$A$8, $A$2:$A$8, "No match", 2, -1)
=XLOOKUP(1800; $A$2:$A$8; $A$2:$A$8; "No match"; 2; -1)
Result: No match
=XLOOKUP(1800, $A$2:$A$8, $A$2:$A$8, "No match", 3, 1)
=XLOOKUP(1800; $A$2:$A$8; $A$2:$A$8; "No match"; 3; 1)
Result: No match
=XLOOKUP(1800, $A$2:$A$8, $A$2:$A$8, "No match", 3, -1)
=XLOOKUP(1800; $A$2:$A$8; $A$2:$A$8; "No match"; 3; -1)
Result: No match
=XLOOKUP(1800, $A$2:$A$8, $B$2:$B$8, "No match", 0, 1)
=XLOOKUP(1800; $A$2:$A$8; $B$2:$B$8; "No match"; 0; 1)
Result: No match
=XLOOKUP(1800, $A$2:$A$8, $B$2:$B$8, "No match", 0, -1)
=XLOOKUP(1800; $A$2:$A$8; $B$2:$B$8; "No match"; 0; -1)
Result: No match
=XLOOKUP(1800, $A$2:$A$8, $B$2:$B$8, "No match", -1, 1)
=XLOOKUP(1800; $A$2:$A$8; $B$2:$B$8; "No match"; -1; 1)
Result: No match
=XLOOKUP(1800, $A$2:$A$8, $B$2:$B$8, "No match", -1, -1)
=XLOOKUP(1800; $A$2:$A$8; $B$2:$B$8; "No match"; -1; -1)
Result: No match
=XLOOKUP(1800, $A$2:$A$8, $B$2:$B$8, "No match", 1, 1)
=XLOOKUP(1800; $A$2:$A$8; $B$2:$B$8; "No match"; 1; 1)
Result: No match
=XLOOKUP(1800, $A$2:$A$8, $B$2:$B$8, "No match", 1, -1)
=XLOOKUP(1800; $A$2:$A$8; $B$2:$B$8; "No match"; 1; -1)
Result: No match
=XLOOKUP(1800, $A$2:$A$8, $B$2:$B$8, "No match", 2, 1)
=XLOOKUP(1800; $A$2:$A$8; $B$2:$B$8; "No match"; 2; 1)
Result: No match
=XLOOKUP(1800, $A$2:$A$8, $B$2:$B$8, "No match", 2, -1)
=XLOOKUP(1800; $A$2:$A$8; $B$2:$B$8; "No match"; 2; -1)
Result: No match
=XLOOKUP(1800, $A$2:$A$8, $B$2:$B$8, "No match", 3, 1)
=XLOOKUP(1800; $A$2:$A$8; $B$2:$B$8; "No match"; 3; 1)
Result: No match
=XLOOKUP(1800, $A$2:$A$8, $B$2:$B$8, "No match", 3, -1)
=XLOOKUP(1800; $A$2:$A$8; $B$2:$B$8; "No match"; 3; -1)
Result: No match
=XLOOKUP(1800, $A$2:$A$8, $C$2:$C$8, "No match", 0, 1)
=XLOOKUP(1800; $A$2:$A$8; $C$2:$C$8; "No match"; 0; 1)
Result: No match
=XLOOKUP(1800, $A$2:$A$8, $C$2:$C$8, "No match", 0, -1)
=XLOOKUP(1800; $A$2:$A$8; $C$2:$C$8; "No match"; 0; -1)
Result: No match
=XLOOKUP(1800, $A$2:$A$8, $C$2:$C$8, "No match", -1, 1)
=XLOOKUP(1800; $A$2:$A$8; $C$2:$C$8; "No match"; -1; 1)
Result: No match
=XLOOKUP(1800, $A$2:$A$8, $C$2:$C$8, "No match", -1, -1)
=XLOOKUP(1800; $A$2:$A$8; $C$2:$C$8; "No match"; -1; -1)
Result: No match
=XLOOKUP(1800, $A$2:$A$8, $C$2:$C$8, "No match", 1, 1)
=XLOOKUP(1800; $A$2:$A$8; $C$2:$C$8; "No match"; 1; 1)
Result: No match
=XLOOKUP(1800, $A$2:$A$8, $C$2:$C$8, "No match", 1, -1)
=XLOOKUP(1800; $A$2:$A$8; $C$2:$C$8; "No match"; 1; -1)
Result: No match
=XLOOKUP(1800, $A$2:$A$8, $C$2:$C$8, "No match", 2, 1)
=XLOOKUP(1800; $A$2:$A$8; $C$2:$C$8; "No match"; 2; 1)
Result: No match
=XLOOKUP(1800, $A$2:$A$8, $C$2:$C$8, "No match", 2, -1)
=XLOOKUP(1800; $A$2:$A$8; $C$2:$C$8; "No match"; 2; -1)
Result: No match
=XLOOKUP(1800, $A$2:$A$8, $C$2:$C$8, "No match", 3, 1)
=XLOOKUP(1800; $A$2:$A$8; $C$2:$C$8; "No match"; 3; 1)
Result: No match
=XLOOKUP(1800, $A$2:$A$8, $C$2:$C$8, "No match", 3, -1)
=XLOOKUP(1800; $A$2:$A$8; $C$2:$C$8; "No match"; 3; -1)
Result: No match
Common mistakes
| Symptom | Cause | Fix |
|---|---|---|
| #N/A although the value is in the list | The lookup value has a trailing space, or it is text while the list holds numbers (or the other way round). | If the list holds numbers, use VALUE(TRIM(E2)): it removes the spaces and turns the text into a number. If the list holds text, TRIM(E2) is enough. |
| #VALUE! in the result | lookup_array and return_array are not the same height, or lookup_array is more than one column wide. | Select the same rows in both, for example A2:A50 and C2:C50, not C2:C51. Click in the formula and press F2: Excel outlines both ranges, so a difference in height shows at once. |
| #SPILL! instead of the result | The answer has several columns or rows, and a cell it needs to fill is not empty. | Clear the cells next to the formula, or make return_array one column wide. |
| A 75-day invoice gets the 91-day rate | match_mode is 1, which takes the next larger band, instead of -1. | Use -1 for “from this value up” bands, and test with a value between two bands. |
| A wrong row or #N/A with search_mode 2 or -2 | A binary search trusts the sort order (low to high for 2, high to low for -2), and the list is no longer sorted, for example after rows were added at the bottom. | Sort the list again, or leave search_mode out: the default search from the first row works on any order. |
Used in combinations
- Find the customer from a bank descriptionCut the invoice number out of a bank statement description with TEXTAFTER and TEXTBEFORE, then look up the customer in the receivables list with XLOOKUP.Excel 2024 or later (This combination needs Excel 2024 or later)
- Find the customer with a regex lookupPull 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.Excel for Microsoft 365 (This combination needs Excel for Microsoft 365)
- Receivables aging total per 30-day bandTurn every due date into a band with XLOOKUP and match_mode -1 on a band table, then let SUM add up the open amounts of one band.Excel 2021 or later (This combination needs Excel 2021 or later)
- Two-way lookup with one XLOOKUP and TOCOLFind the value where a row label and a column header meet, by turning both conditions into one grid of 0s and 1s and flattening it with TOCOL.Excel 2024 or later (This combination needs Excel 2024 or later)