How to Use XLOOKUP to Look Up Multiple Items in One Excel Cell
Question details
The user needs to perform a lookup on a single cell that contains multiple text values (e.g., "Apples and Oranges") and return the corresponding results for each individual item.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Attempting to use XLOOKUP to find matches for a cell containing a delimited string of multiple items instead of a single lookup value.
- Observed behavior
- XLOOKUP searches for the exact full string by default and fails to match the individual items contained within the cell.
Ensure you are using Microsoft 365 or a recent version of Excel (or WPS Office) that supports dynamic array functions like TEXTSPLIT and XLOOKUP. Check the delimiter used in your cell (e.g., a comma or the word 'and') so you can split the text accurately.
Combine TEXTSPLIT, XLOOKUP, and TEXTJOIN to Retrieve Multiple Results
This method splits the cell's contents into an array, looks up each item individually using XLOOKUP, and then joins the results back into a single cell.
Because XLOOKUP cannot inherently parse multiple items grouped in a single text string, you must first break the string into an array. TEXTSPLIT handles the separation, XLOOKUP fetches the corresponding values, and TEXTJOIN compiles the results cleanly.
Identify the delimiter in your cell (e.g., " and " or ", "). Use the TEXTSPLIT function to break the text into an array. For example: TEXTSPLIT(A2, " and ").
Wrap the TEXTSPLIT formula inside XLOOKUP to find the matching values in your lookup array. Format: =XLOOKUP(TEXTSPLIT(A2," and "), D2:D10, E2:E10, "").
To prevent the results from spilling into adjacent cells, wrap the entire formula in TEXTJOIN. Final formula: =TEXTJOIN(", ", TRUE, XLOOKUP(TEXTSPLIT(A2, " and "), D2:D10, E2:E10, "")). Press Enter to apply.
Use OR/AND with XLOOKUP for Logical Tests
If your lookup table returns TRUE/FALSE or numeric conditions, you can test if any or all split items meet the criteria.
Handle Dynamic Arrays Seamlessly with WPS Spreadsheet
WPS Office Spreadsheet fully supports advanced dynamic array functions like XLOOKUP and TEXTJOIN. It is a powerful, lightweight, and free alternative that handles complex data processing just like Microsoft Excel.
- 1. Open WPS Spreadsheet: Launch WPS Office and create or open your existing .xlsx workbook.
- 2. Enter the formula: Click on the target cell and type your combined =TEXTJOIN(", ", TRUE, XLOOKUP(...)) formula.
- 3. Press Enter to execute: Hit Enter, and WPS Spreadsheet will dynamically split the text, look up each value, and combine the results instantly.
Frequently Asked Questions
Why is my XLOOKUP returning a #NAME? error?
The #NAME? error typically occurs if your version of Excel does not support XLOOKUP or TEXTSPLIT. You must be using Microsoft 365 or a newer version (like Excel 2021) that includes these modern dynamic array functions.
Can I use XLOOKUP with multiple delimiters, like both commas and spaces?
Yes, TEXTSPLIT allows you to specify multiple delimiters using an array constant. For example, =TEXTSPLIT(A2, {","," and "}) will split the text at either a comma or the word 'and', which XLOOKUP can then process.
What happens if one of the multiple items is not found by XLOOKUP?
You can use the fourth argument in XLOOKUP (the [if_not_found] argument) to handle missing items. For instance, =XLOOKUP(TEXTSPLIT(A2,", "), D2:D10, E2:E10, "Not Found") will return 'Not Found' for the specific item that isn't in the lookup array.
How do I prevent the XLOOKUP results from spilling into multiple columns?
When XLOOKUP receives an array of lookup values from TEXTSPLIT, it spills the results into adjacent cells by default. To prevent this, wrap the XLOOKUP function in a TEXTJOIN function to combine all matched results into a single cell.
