Function Problems

How to Use Wildcard Matching in XLOOKUP Against a Table Array

Amos Gikunda Oct 9, 2026 868 views

Question details

The user needs to perform a lookup where a partial value matches a related or longer value in a table array (e.g., matching 'FIRST_NAME' with 'APPROVER_FIRST_NAME') rather than requiring an exact match.

How to Use Wildcard Matching in XLOOKUP Against a Table Array

Product: Spreadsheets
Device & OS: not provided
Scenario: Data analysis and matching related identifiers between two different data tables where exact names or strings are not identical but contain the core search term.
Observed behavior: By default, XLOOKUP performs an exact match. Without explicit configuration or advanced matching features, partial strings within a table array will return an error or 'Not found'.

Before you start

Ensure your spreadsheet application supports the XLOOKUP function (available in newer versions of Excel and WPS Spreadsheet). Also, verify that your lookup values are specific enough to prevent false matches with unrelated words.

Solution 1Recommended

Use XLOOKUP with Wildcard Match Mode

This is the most direct method to find partial string matches. By appending wildcard characters and setting the correct match mode, XLOOKUP can find values containing the lookup text.

The asterisk (*) acts as a wildcard representing any number of characters. By combining the lookup cell with asterisks and changing the match-mode argument to 2, XLOOKUP will search the table array for any cell that contains the lookup value.

Select the output cell

Click on the cell where you want the lookup result to be displayed.

Enter the XLOOKUP formula

Type the formula: =XLOOKUP("*"&A2&"*", LookupRange, ReturnRange, "Not found", 2). Replace 'A2' with your lookup value, 'LookupRange' with the column you are searching, and 'ReturnRange' with the column containing the data you want to retrieve.

Apply and verify

Press Enter. Check the result to ensure it correctly identified the partial match. Drag the fill handle down to apply the formula to other rows if necessary.

Match Mode 2: The '2' at the end of the formula is crucial—it instructs XLOOKUP to interpret the asterisks as wildcards rather than literal text characters.

Solution 2

Use Fuzzy Lookup in Power Query for Complex Matches

When simple wildcard matching is not sufficient (e.g., matching typos or similar text structures), Power Query's fuzzy matching calculates text similarity.

Easily Perform Advanced Lookups with WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like XLOOKUP, allowing you to seamlessly perform complex data analysis and partial text matching just as you would in Microsoft Excel.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing your lookup data.
2. Start the XLOOKUP function: Select an empty cell and type =XLOOKUP( to begin the formula.
3. Apply the wildcard syntax: Select your lookup cell and combine it with asterisks, for example: "*"&A2&"*".
4. Define the ranges and match mode: Select your lookup array, then your return array, add a "Not found" fallback, and type 2 as the final argument to enable wildcard matching.
5. Execute the formula: Press Enter to instantly fetch your partially matched data from the table array.

Natively supports XLOOKUP, VLOOKUP, and wildcard character matching.100% format compatibility with Microsoft Excel (.xlsx, .xls, .csv).Free, lightweight, and fast performance even with large datasets.Familiar user interface ensuring zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my XLOOKUP wildcard match returning the wrong result?

Because wildcards match any sequence of characters, a short lookup value might inadvertently match a different string that happens to contain those same letters. Ensure your search term is specific enough, or carefully structure your data to prevent overlapping partial strings.

What does the number 2 mean in the XLOOKUP formula?

The number 2 in the fifth argument of XLOOKUP tells the function to use wildcard character matching. If you omit it or use 0, XLOOKUP will look for an exact match and treat the asterisks as literal characters.

Can I use wildcards other than the asterisk (*) in XLOOKUP?

Yes, you can use a question mark (?) to match any single character, or a tilde (~) to escape wildcards if you need to search for an actual asterisk or question mark within your text.

Is XLOOKUP available in older versions of Excel?

No, XLOOKUP is only available in Microsoft 365, Excel 2021, and modern spreadsheet software like WPS Office. If you are using an older version, you must use VLOOKUP or INDEX/MATCH combined with wildcards to achieve a similar result.

Excel & Data Problems