Combine Multiple Matching Values in One Excel Cell with TEXTJOIN
Question details
Combine values from multiple rows that match two criteria into a single Excel cell.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user wants to return every value associated with matching criteria rather than only the first matching row.
- Observed behavior
- Matching values should appear together in one cell, separated by commas, without using a custom VBA function.
Check that your Excel version supports TEXTJOIN, and identify the two criteria columns and the column containing the values to combine. The example uses Sheet1 columns A, B, and D, with criteria in Sheet2 cells A6 and D4.
Use TEXTJOIN and IF to combine values matching two criteria
Use this formula to join all matching values from a specified range into one cell.
On Sheet2, click the cell where you want the combined values to appear.
In the formula bar, enter =TEXTJOIN(", ",TRUE,IF(Sheet1!$A$1:$A$200=Sheet2!$A6,IF(Sheet1!$B$1:$B$200=Sheet2!D$4,Sheet1!$D$1:$D$200,""),""))). Change the ranges and criteria references to match your worksheet.
Press Enter in Microsoft 365. In older Excel versions that support TEXTJOIN but require array entry, press Ctrl+Shift+Enter instead.
Compare the destination cell with the matching rows on Sheet1. Values from column D should appear in row order, separated by commas.
Combine matching values in WPS Spreadsheets
In a version of WPS Spreadsheets that supports TEXTJOIN, use the same IF-based formula to combine matching rows in one cell.
- 1. Open your workbook: Open WPS Spreadsheets and choose Open to select your Excel workbook.
- 2. Select an output cell: Click the cell where you want the matching values to appear.
- 3. Enter the formula: In the formula bar, enter =TEXTJOIN(", ",TRUE,IF(Sheet1!$A$1:$A$200=Sheet2!$A6,IF(Sheet1!$B$1:$B$200=Sheet2!D$4,Sheet1!$D$1:$D$200,""),""))), adjusting the references for your workbook.
- 4. Confirm and review: Press Enter and check the result against the source rows. If your WPS version requires array entry, confirm the formula with Ctrl+Shift+Enter.

Frequently Asked Questions
Why does the formula return a blank cell?
Check that both criteria match the values in Sheet1 columns A and B and that the corresponding cells in column D are not empty. Also check for extra spaces or differences between numbers and text.
How do I return values from column C instead of column D?
In the formula bar, replace Sheet1!$D$1:$D$200 with Sheet1!$C$1:$C$200. Keep the result range aligned with the criteria ranges.
Do I always need to press Ctrl+Shift+Enter?
No. Microsoft 365 can evaluate the formula with Enter. Older Excel versions that support TEXTJOIN but do not evaluate arrays dynamically may require Ctrl+Shift+Enter.
Will TEXTJOIN remove duplicate matches?
No. The formula includes each matching value in row order, including duplicates. Removing duplicates requires an additional approach, such as applying UNIQUE in a version of Excel that supports it.




