Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to use XLOOKUP with wildcards in Excel and Google Sheets
Topic:XLOOKUPExcel & Google Sheets
=XLOOKUP("*apple*", A2:A4, B2:B4, "Not Found")Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Enter your lookup value with wildcards (* for any characters, ? for one character) in the formula.
- 2Specify the range where you want to search for the lookup value.
- 3Define the range from which you want to return the corresponding value.
- 4Provide an optional value to return if no match is found.
Tips
- Ensure your wildcard usage matches the data format to avoid unexpected results.
- In Excel, XLOOKUP is available in Excel 2019 and later versions; check your version compatibility.
- Google Sheets supports XLOOKUP starting from 2020; verify your Sheets version if encountering issues.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What are the wildcards used in XLOOKUP?
The asterisk (*) represents zero or more characters, while the question mark (?) represents a single character.
Does XLOOKUP support case-sensitive searches?
No, XLOOKUP performs case-insensitive searches by default. Use the match_mode argument set to -1 for case-sensitive matching.
Can I use multiple wildcards in a single lookup value?
Yes, you can combine multiple wildcards (* and ?) within a single lookup value to refine your search criteria.
More on XLOOKUP
See all →Reference
XLOOKUP
The modern replacement for VLOOKUP and INDEX/MATCH — look up a value and return a match in any direction.
How-toHow to use XLOOKUP with multiple criteria
Concatenate criteria columns in XLOOKUP's lookup array to match on multiple conditions at once, returning results only when all criteria align.
Error fixFix #N/A
Fix #N/A errors in XLOOKUP in Excel and Google Sheets — lookup value not found, data-type mismatch, incorrect array size, and match mode issues.
How-toHow to return multiple columns with XLOOKUP
Learn how to use XLOOKUP to fetch data from multiple columns efficiently in both Excel and Google Sheets.
Error fixFix #SPILL!
Fix #SPILL! errors from XLOOKUP in Excel — spill range blocked by non-empty cells, merged cells, or returning an array into a table column.