Excel Formula: Match Multiple Names and Combine Results in One Cell
Question details
The user needs to look up values associated with multiple names from another worksheet and merge all the corresponding results into a single cell, typically separated by commas.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Looking up multiple criteria in one worksheet to retrieve corresponding group names or specific data from another sheet, and displaying them neatly grouped together.
- Observed behavior
- Instead of returning results into separate individual cells, the goal is to successfully combine all matched lookup results into one single output cell.
Ensure that the lookup names exist exactly as written in the target worksheet to avoid #N/A errors. Note that the TEXTJOIN function requires Excel 2016 or a newer version like WPS Office.
Use TEXTJOIN with Array MATCH (Modern Excel Versions)
Combine multiple lookups into a single elegant formula using array evaluation to pull and format all data instantly.
In modern spreadsheet environments, you can feed an array of lookup values into the MATCH function. The TEXTJOIN function will then process the returned array and concatenate the results automatically, ignoring empty values.
Click on the cell where you want the combined, comma-separated results to appear.
Type the formula: =TEXTJOIN(", ",TRUE,INDEX(Sheet2!I:I,MATCH(A1:C1,Sheet2!A:A,0)))
Press Enter to execute. If you are using an environment that does not support dynamic arrays automatically, you may need to press Ctrl+Shift+Enter.
Use Separate INDEX and MATCH Functions (Older Excel Versions)
If your spreadsheet software struggles with array matches, you can nest individual INDEX/MATCH formulas inside a single TEXTJOIN function.
Easily Combine Data with WPS Office
WPS Spreadsheet fully supports advanced array formulas, TEXTJOIN, INDEX, and MATCH, allowing you to seamlessly look up and combine multiple values. It is highly efficient and offers a familiar, user-friendly interface.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your lookup names and reference tables.
- 2. Select the output cell: Click on the specific cell where you want the combined names to appear.
- 3. Enter the formula: Paste your TEXTJOIN and INDEX/MATCH formula directly into the formula bar.
- 4. View your results: Press Enter to instantly retrieve and merge your target data from the secondary sheet.
Frequently Asked Questions
Why does my INDEX MATCH formula return an #N/A error?
This usually happens when the lookup name does not exactly match the data in the source column. Check for trailing spaces, spelling discrepancies, or formatting differences between the two sheets.
Can I use VLOOKUP instead of INDEX and MATCH?
Yes, you can place VLOOKUP functions inside the TEXTJOIN formula. However, INDEX and MATCH is generally preferred because it is more flexible and does not require the lookup column to be on the far left of your data array.
What if my software version doesn't support the TEXTJOIN function?
If you are using a very old version of Excel (prior to 2016) that lacks TEXTJOIN, you can manually combine the results using the ampersand (&) symbol. For example: =INDEX(...) & ", " & INDEX(...) & ", " & INDEX(...).
