In this Excel XLOOKUP Wildcard Tutorial, you learn how to create an Excel XLOOKUP wildcard formula, where the XLOOKUP function considers wildcards when searching for a match.
The XLOOKUP function is available in Excel 2021 and later (including Excel 365).
This Excel XLOOKUP Wildcard Tutorial is accompanied by an Excel workbook with the data and formulas I use. Get this example workbook (for free) by clicking the button below.
Table of Contents
Related Excel Training Materials and Resources
The following Excel VLookup Tutorials may help you better understand the differences between the XLOOKUP and VLOOKUP functions (XLOOKUP vs. VLOOKUP).
- Excel VLOOKUP Tutorial (under development): Click here to open.
- Excel VLOOKUP from Another Sheet: Click here to open.
- Excel VLOOKUP Compare 2 Columns and Find Matches: Click here to open.
- Excel VLookup Sum Multiple Row Values (in Same Column): Click here to open.
- Excel VLOOKUP Multiple Columns: Click here to open.
- Excel VLOOKUP Sum Multiple Columns (Values): Click here to open.
- Excel VLookup Sum Multiple Column Values (with XLOOKUP): Click here to open.
- Excel VLookup Sum Multiple Rows and Columns: Click here to open.
- Excel VLookup Multiple Criteria with INDEX MATCH: Click here to open.
- Excel VLookup Multiple Criteria with XLOOKUP: Click here to open.
- Excel VLookup Multiple Criteria with the FILTER Function: Click here to open.
- Excel VLOOKUP Return Multiple Values with Helper Column: Click here to open.
- Excel VLookup Return Multiple Values with the INDEX Function: Click here to open.
- Excel VLookup Return Multiple Values with the FILTER Function: Click here to open.
- Excel VLookup Return Multiple Values in One Cell Separated by a Comma: Click here to open.
- Excel VLOOKUP Multiple Sheets: Click here to open.
- Excel VLOOKUP Multiple Sheets in Different Workbook: Click here to open.
- Excel VLOOKUP Sheet in Multiple Different Workbooks: Click here to open.
This Excel XLOOKUP Wildcard Tutorial is part of a more comprehensive series of Excel XLOOKUP Tutorials.
- Excel XLOOKUP Tutorial: Click here to open.
- Excel XLOOKUP in Table: Click here to open.
- Excel Nested XLOOKUP (Dynamic Lookup Value): Click here to open.
- Excel XLOOKUP If Not Found Return Blank: Click here to open.
- Excel IF XLOOKUP (for Error Handling): Click here to open.
- Excel XLOOKUP If Blank Return Blank: Click here to open.
- Excel XLOOKUP Wildcard: Click here to open.
- Excel XLOOKUP Between 2 Values: Click here to open.
- Excel XLookup Return All Matches: Click here to open.
You can find more Excel and VBA Tutorials in the organized Tutorials Archive: Click here to visit the Archives. The following are some of my most popular Excel Tutorials and Training Resources:
- Excel Keyboard Shortcuts Cheat Sheet: Click here to open.
- Excel Power Query (Get & Transform) Tutorial for Beginners: Click here to open.
- Excel Macro Tutorial for Beginners: Click here to open.
If you want to learn more about Excel essentials, Excel formulas, and similar Excel topics, you may be interested in taking one (or more) Excel Courses: Click here to learn more about these Excel Courses (affiliate link).
If you want to learn how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:
- Premium Courses at the Power Spreadsheets Academy: Click here to open.
- Books at the Power Spreadsheets Library: Click here to open.
If you need help with Excel tasks/projects, you may be interested in working with me: Click here to learn more about working with me.
Excel XLOOKUP Wildcard Formula Template/Structure
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-wildcard/
=XLOOKUP(LookupValueWithWildcards,ColumnOrRowWhereYouSearch,ColumnOrRowWithValueToReturn,,2)
Step-by-Step Process to Create an Excel XLOOKUP Wildcard Formula
(1) Call the XLOOKUP function.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-wildcard/
=XLOOKUP(...)
(2) Use the first argument of the XLOOKUP function (lookup_value) to specify the lookup value. This is the value you search for in the column (when doing a VLookup) or row (when doing an HLookup) you search in (and specify in step #3).
To create an XLOOKUP wildcard formula, use the following wildcard characters (as appropriate) when specifying the lookup value:
- Question mark (?): Represents any single character.
- Asterisk (*): Represents any sequence of characters.
- Tilde (~) followed by one of the following characters:
- Question mark (~?): Represents a question mark (?).
- Asterisk (~*): Represents an asterisk (*).
- Tilde (~~): Represents a tilde (~).
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-wildcard/
=XLOOKUP(LookupValueWithWildcards,...)
(3) Use the second argument of the XLOOKUP function (lookup_array) to specify the lookup array. This is the column (when doing a VLookup) or row (when doing an HLookup) where you search for the lookup value (you specified in step #2).
As a general rule: Specify the lookup array as:
- A single-column (1 column, several rows) cell range, when doing a VLookup; or
- A single-row (1 row, several columns) cell range, when doing an HLookup.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-wildcard/
=XLOOKUP(LookupValueWithWildcards,ColumnOrRowWhereYouSearch,...)
(4) Use the third argument of the XLOOKUP function (return_array) to specify the return array. This is the column (when doing a VLookup) or row (when doing an HLookup) with the value to return.
Ensure the number of rows (when doing a VLookup) or columns (when doing an HLookup) of the return array (you specify in this step #4) is the same as that of the lookup array (you specified in step #3).
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-wildcard/
=XLOOKUP(LookupValueWithWildcards,ColumnOrRowWhereYouSearch,ColumnOrRowWithValueToReturn,...)
(5) Omit the fourth argument of the XLOOKUP function (if_not_found). This argument is:
- The text returned by the XLOOKUP function if it fails to find a valid match for the lookup value (you specified in step #2); and
- Is optional.
If you omit the if_not_found argument (as in this XLOOKUP wildcard formula template/structure) and the XLOOKUP function fails to find a valid match for the lookup value (you specified in step #2), XLOOKUP returns the #N/A error.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-wildcard/
=XLOOKUP(LookupValueWithWildcards,ColumnOrRowWhereYouSearch,ColumnOrRowWithValueToReturn,,...)
(6) Set the fifth argument of the XLOOKUP function (match_mode) to 2. This ensures the XLOOKUP function:
- Carries out a wildcard match; and
- Considers the wildcard(s) you used when specifying the lookup value (in step #2) when searching for a match.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-wildcard/
=XLOOKUP(LookupValueWithWildcards,ColumnOrRowWhereYouSearch,ColumnOrRowWithValueToReturn,,2)
How (and Why) the XLOOKUP Wildcard Formula Works
You use the fifth argument of the XLOOKUP function (match_mode) to specify:
- XLOOKUP's match mode.
- How XLOOKUP matches the lookup value against the values in the lookup array.
Explicitly setting XLOOKUP's match mode to “2” (as in the XLOOKUP wildcard formula template/structure above) ensures the XLOOKUP function:
- Carries out a wildcard match; and
- Considers the wildcard(s) you use when specifying the lookup value when searching for a match.
Once you set XLOOKUP's match mode to “2” (as in the XLOOKUP wildcard formula template/structure above), you can use wildcard characters (?, *, ~) when specifying the lookup value.
Excel XLOOKUP Wildcard Example Formula
This Excel XLOOKUP Wildcard Tutorial is accompanied by an Excel workbook with the data and formulas I use. Get this example workbook (for free) by clicking the button below.
Excel XLOOKUP Wildcard Example Worksheet
The example worksheet has 3 tables/sections with the following characteristics:
(1) Table 1 (cells A6 to H26).
The table:
- With the data.
- Where I search with the XLOOKUP wildcard example formula.
(2) Lookup value (cells J6 and K6).
The lookup value (S* 20) is stored in cell K6.
(3) XLOOKUP wildcard example formula (cells J8 to K9).
- Cell J9:
- Stores the XLOOKUP wildcard example formula; and
- Displays the results.
- Cell K9 displays the XLOOKUP wildcard example formula I enter in cell J9.

Excel XLOOKUP Wildcard Example Formula
The XLOOKUP wildcard example formula stored in cell J9 is as follows:
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-wildcard/
=XLOOKUP(K6,A6:A26,H6:H26,,2)
The lookup value (stored in cell K6) is “S* 20”. Therefore, the XLOOKUP wildcard example formula searches (in column A, cells A6 to H26) for a string that:
- Starts with the letter S;
- Followed by any sequence of characters (represented by the asterisk, *); and
- Ends with a space followed by the number 20 ( 20).
The first entry (in column A) matching the wildcard lookup value is “Salesperson 20”.

Download the Excel XLOOKUP Wildcard Example Workbook
This Excel XLOOKUP Wildcard Tutorial is accompanied by an Excel workbook with the data and formulas I use. Get this example workbook (for free) by clicking the button below.
Related Excel Training Materials and Resources
The following Excel VLookup Tutorials may help you better understand the differences between the XLOOKUP and VLOOKUP functions (XLOOKUP vs. VLOOKUP).
- Excel VLOOKUP Tutorial (under development): Click here to open.
- Excel VLOOKUP from Another Sheet: Click here to open.
- Excel VLOOKUP Compare 2 Columns and Find Matches: Click here to open.
- Excel VLookup Sum Multiple Row Values (in Same Column): Click here to open.
- Excel VLOOKUP Multiple Columns: Click here to open.
- Excel VLOOKUP Sum Multiple Columns (Values): Click here to open.
- Excel VLookup Sum Multiple Column Values (with XLOOKUP): Click here to open.
- Excel VLookup Sum Multiple Rows and Columns: Click here to open.
- Excel VLookup Multiple Criteria with INDEX MATCH: Click here to open.
- Excel VLookup Multiple Criteria with XLOOKUP: Click here to open.
- Excel VLookup Multiple Criteria with the FILTER Function: Click here to open.
- Excel VLOOKUP Return Multiple Values with Helper Column: Click here to open.
- Excel VLookup Return Multiple Values with the INDEX Function: Click here to open.
- Excel VLookup Return Multiple Values with the FILTER Function: Click here to open.
- Excel VLookup Return Multiple Values in One Cell Separated by a Comma: Click here to open.
- Excel VLOOKUP Multiple Sheets: Click here to open.
- Excel VLOOKUP Multiple Sheets in Different Workbook: Click here to open.
- Excel VLOOKUP Sheet in Multiple Different Workbooks: Click here to open.
This Excel XLOOKUP Wildcard Tutorial is part of a more comprehensive series of Excel XLOOKUP Tutorials.
- Excel XLOOKUP Tutorial: Click here to open.
- Excel XLOOKUP in Table: Click here to open.
- Excel Nested XLOOKUP (Dynamic Lookup Value): Click here to open.
- Excel XLOOKUP If Not Found Return Blank: Click here to open.
- Excel IF XLOOKUP (for Error Handling): Click here to open.
- Excel XLOOKUP If Blank Return Blank: Click here to open.
- Excel XLOOKUP Wildcard: Click here to open.
- Excel XLOOKUP Between 2 Values: Click here to open.
- Excel XLookup Return All Matches: Click here to open.
You can find more Excel and VBA Tutorials in the organized Tutorials Archive: Click here to visit the Archives. The following are some of my most popular Excel Tutorials and Training Resources:
- Excel Keyboard Shortcuts Cheat Sheet: Click here to open.
- Excel Power Query (Get & Transform) Tutorial for Beginners: Click here to open.
- Excel Macro Tutorial for Beginners: Click here to open.
If you want to learn more about Excel essentials, Excel formulas, and similar Excel topics, you may be interested in taking one (or more) Excel Courses: Click here to learn more about these Excel Courses (affiliate link).
If you want to learn how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:
- Premium Courses at the Power Spreadsheets Academy: Click here to open.
- Books at the Power Spreadsheets Library: Click here to open.
If you need help with Excel tasks/projects, you may be interested in working with me: Click here to learn more about working with me.