How to Lookup Comma-Separated Criteria and Return Multiple Results in Excel
Question details
The user needs to match multiple comma-separated values from a single cell against a lookup column and return the corresponding values as a single comma-separated string.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Looking up multiple items stored in one cell, separated by commas, and fetching corresponding data from a table.
- Observed behavior
- Standard TEXTJOIN and IF formulas return FALSE or fail when the lookup criteria cell contains multiple comma-separated values.
Ensure you are using Microsoft Excel 365, as the dynamic array formulas required for the primary solution (like TEXTSPLIT and FILTER) are not available in older versions.
Use Dynamic Array Formulas (TEXTJOIN, FILTER, XMATCH, TEXTSPLIT)
The most efficient way for Excel 365 users to handle multiple criteria in a single cell.
This formula combines modern Excel functions to split the criteria cell, match against your data table, filter the corresponding results, and join them back together while ignoring duplicates.
Assume your criteria cell is A2, lookup column is D2:D6, and return column is E2:E6.
Click on the cell where you want the results and enter: =TEXTJOIN(", ",TRUE,UNIQUE(FILTER($E$2:$E$6,ISNUMBER(XMATCH($D$2:$D$6,TRIM(TEXTSPLIT(A2,",")))),"")))
Press Enter to evaluate. Excel will automatically split the comma-separated criteria, find matches, and join the unique results.
Create a Custom VBA Function (MLookUp)
Ideal for older versions of Excel or users who prefer a dedicated custom function.
Handle Complex Lookups Easily with WPS Spreadsheet
WPS Office provides powerful spreadsheet capabilities that support advanced array formulas and functions, helping you manage complex data lookups seamlessly and for free.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the dataset.
- 2. Enter the lookup formula: Select the target cell and input your dynamic array formula to fetch multiple criteria.
- 3. Save your progress: Press Ctrl+S to save your work smoothly without compatibility issues.
Frequently Asked Questions
Why does my standard VLOOKUP fail with comma-separated criteria?
VLOOKUP treats the entire cell contents as a single text string. It searches for an exact match of the whole comma-separated list rather than evaluating each item individually.
What does the TEXTSPLIT function do in this formula?
TEXTSPLIT takes the single text string in your cell and divides it into an array of individual values based on a specified delimiter, like a comma, allowing functions like XMATCH to process each value separately.
Can I use the VBA Scripting.Dictionary solution on Excel for Mac?
Yes, you can use VBA on Excel for Mac, but note that the 'Scripting.Dictionary' object used for removing duplicates is a Windows-only feature. A Mac-compatible VBA script would need to use a VBA Collection instead.
