How to Use XLOOKUP for Partial Text Match Between Worksheets
Question details
The user needs to retrieve data from another worksheet when the lookup value is only a partial match (a substring) of the target text.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Looking up and pulling data across different worksheets where the source data contains a longer text string, and the search criteria is just a part of that string (e.g., a short username within a longer name field).
- Observed behavior
- Standard lookup functions require exact matches by default, which fails to retrieve the corresponding row data when only a partial text match exists.
Ensure that your spreadsheet software supports the XLOOKUP function, which is available in modern versions of WPS Spreadsheet and Excel, and keep both your source and destination worksheets open.
Use XLOOKUP with ISNUMBER and SEARCH Functions
Combine XLOOKUP with ISNUMBER and SEARCH to locate a substring within a range and return the corresponding data from another worksheet.
By nesting the SEARCH function inside ISNUMBER, you create an array of TRUE/FALSE values based on whether the partial text exists within the target cells. XLOOKUP then simply searches for the first TRUE value to pull the corresponding data.
Click on the cell in your current worksheet where you want the retrieved data to be displayed.
Type the formula: =XLOOKUP(TRUE,ISNUMBER(SEARCH(A2,Sheet2!$H$2:$H$14)),Sheet2!$I$2:$M$14). This tells the system to find a TRUE result.
Replace 'A2' with your specific lookup value. Change 'Sheet2!$H$2:$H$14' to the worksheet range containing the longer text strings, and 'Sheet2!$I$2:$M$14' to the array you want to return.
Press Enter to execute the formula. The matching data from the other worksheet will instantly populate in your selected cell.
Perform Advanced Lookups Easily with WPS Spreadsheet
WPS Office fully supports modern spreadsheet functions like XLOOKUP, making it incredibly easy to perform complex data analysis, partial text matches, and cross-sheet lookups.
- 1. Download and install: Get WPS Office for free from the official website and install it on your device.
- 2. Open your spreadsheet: Launch WPS Spreadsheet and open your .xlsx file containing the multiple worksheets.
- 3. Apply XLOOKUP: Select the destination cell and enter your XLOOKUP formula with ISNUMBER and SEARCH just as you would in Excel.
- 4. Retrieve data: Press Enter to instantly fetch and spill your cross-sheet data seamlessly.
Frequently Asked Questions
Can I use wildcards with XLOOKUP instead of SEARCH?
Yes, XLOOKUP natively supports wildcards. You can use an asterisk (*) around your cell reference (e.g., "*"&A2&"*") as the lookup_value, and set the match_mode argument to 2. This is a great alternative to the ISNUMBER and SEARCH combination for partial matches.
Why does my XLOOKUP formula return an #N/A error?
The #N/A error occurs when the formula cannot find the specified substring in the target range. You can handle this gracefully by adding a custom message to the if_not_found argument of the XLOOKUP function, such as =XLOOKUP(TRUE, ..., ..., "User not found").
How do I pull multiple columns of data with one XLOOKUP formula?
XLOOKUP naturally spills data across adjacent cells if the return array spans multiple columns. Just ensure your return array (e.g., $I$2:$M$14) covers all the columns you want to extract, and the function will populate them automatically.
What if my spreadsheet software doesn't support XLOOKUP?
XLOOKUP is a newer function. If you are using an older version of your spreadsheet software, you can achieve the same partial match result by using a combination of INDEX, MATCH, ISNUMBER, and SEARCH entered as an array formula.
