XLOOKUP Wildcards and Match Modes Explained

An XLOOKUP wildcard search finds a partial match, like a customer whose name starts with "Riverside", but only if you set the 5th argument, match_mode, to 2. The same argument also handles bands: -1 finds the next smaller value and 1 the next larger. This guide explains each match mode with examples you can type in. XLOOKUP needs Excel 2021, Excel 2024 or Microsoft 365.

How to use an XLOOKUP wildcard match#

Set match_mode to 2, and XLOOKUP reads * and ? as wildcards. Here is a courier desk's customer list:

To find the account of the customer whose name starts with Riverside:

=XLOOKUP("Riverside*",A2:A9,B2:B9,"Not found",2)

The result is AC-2005. Each part has a job:

• *"Riverside"**: the pattern. The * means "any characters after this".
• A2:A9 and B2:B9: search the names, bring back the accounts.
• "Not found": the 4th argument, if_not_found. You need something here (or an empty gap) to reach the 5th.
• 2: match_mode, wildcard match.

Without the 2, XLOOKUP searches for the exact text "Riverside*", asterisk included, and returns "Not found". You can also skip the 4th argument with two commas: =XLOOKUP("Riverside*",A2:A9,B2:B9,,2). Then a missing name shows #N/A.

The three wildcard characters#

Excel has three wildcard characters, and they work on text only:

The ? is handy for codes with a fixed shape. "AC-20??" matches any code with exactly two characters after AC-20, like AC-2001 or AC-2099, but not AC-200, which is one character short. Wildcards are case-insensitive, so "riverside*" finds Riverside Bakery too. They don't work on real numbers: a pattern like "20*" won't match the number 2026, only text that starts with 20.

Search for a name that contains a word#

To make the search reusable, put the word in a cell and join asterisks to both sides with &. With "Pine" in H11:

=XLOOKUP("*"&H11&"*",A2:A9,B2:B9,"Not found",2)

The pattern becomes *Pine*, "Pine anywhere in the name", and the result is AC-2004 for Pixel & Pine Studio. Type a different word in H11 and the answer updates. A common slip is writing "*H11*" inside one pair of quotes. That searches for the letters H, 1, 1, not for the word in the cell.

XLOOKUP returns the first match#

When several names fit the pattern, XLOOKUP returns the first one from the top. Two customers have Dental in their name:

• =XLOOKUP("*Dental*",A2:A9,B2:B9,"Not found",2) returns AC-2003, Northgate Dental.
• To get Bright Smile Dental, use a pattern only it fits: "Bright*" returns AC-2007.
• Or search from the bottom with search_mode -1, the 6th argument: =XLOOKUP("*Dental*",A2:A9,B2:B9,"Not found",2,-1) also returns AC-2007.

So check your pattern against the whole list. A loose pattern can return the wrong customer with no error.

How to return every match, not just the first#

XLOOKUP always returns one result. To list every customer that fits a word, use the FILTER function with SEARCH (Excel 2021 or Microsoft 365):

=FILTER(B2:B9,ISNUMBER(SEARCH("Dental",A2:A9)),"None")

SEARCH looks for "Dental" inside each name and returns a position number, or an error when it isn't there. ISNUMBER turns that into TRUE or FALSE, and FILTER keeps the TRUE rows. The result spills into two cells: AC-2003 and AC-2007. SEARCH ignores case, like the wildcards do. The cells below the formula must be empty, or you get #SPILL!.

Match modes -1 and 1: bands and tiers#

Match modes -1 and 1 return a nearby value when there is no exact match. Here is a rate card where each band starts at a weight, and a box list where each box has a maximum weight:

With the rates in D2:E6, the boxes in D10:E13 and a parcel weight in H2:

• -1, exact or next smaller, finds the rate band: =XLOOKUP(H2,$D$2:$D$6,$E$2:$E$6,,-1). A 7.5 kg parcel gets 11.00, from the 5 kg band.
• 1, exact or next larger, finds the smallest box that fits: =XLOOKUP(H2,$E$10:$E$13,$D$10:$D$13,,1). A 7.5 kg parcel gets box M, because 8 is the next limit up.

Unlike VLOOKUP with TRUE, these modes don't need a sorted list. If you work in older Excel, the VLOOKUP approximate match guide covers the TRUE version and its sorting rule.

Wildcards in VLOOKUP, HLOOKUP and MATCH#

Older lookups accept wildcards without any setting. With an exact match, VLOOKUP and HLOOKUP read * and ? in text as wildcards, and so does MATCH with 0:

=VLOOKUP("Riverside*",A2:B9,2,FALSE)

This also returns AC-2005. The difference is that XLOOKUP makes you ask for wildcards, so a real asterisk in your data can't cause a surprise. XMATCH, the modern MATCH, uses the same match modes as XLOOKUP. See our beginner's guide to VLOOKUP and the HLOOKUP guide for the older functions, and the XLOOKUP function guide for a quick reference.

Common mistakes with XLOOKUP match modes#

• Forgetting match_mode 2. The asterisk is treated as text and you get #N/A or your not-found message.
• Putting the 2 in the 4th place. =XLOOKUP("Riverside*",A2:A9,B2:B9,2) makes 2 the not-found value, and it does an exact search. Use ,,2 or fill the 4th argument first.
• A pattern that is too loose. "*a*" matches almost every name. Use a part of the name no other row shares.
• Mixing up -1 and 1. -1 rounds down to the band that starts below the value. 1 rounds up to the next limit.

Practise it#

Match modes make sense quickly once you see them change the answer. ExcelReady's XLOOKUP wildcards and match modes lesson uses the courier desk from this guide: find accounts by partial names, build a "contains" search from a cell, avoid the first-match trap, then place parcels into rate bands and boxes with -1 and 1. Try the product search on this page, then practise XLOOKUP match modes in a live spreadsheet.