Function Problems

Combine Multiple Matching Values in One Excel Cell with TEXTJOIN

Aamir Naveed AkramAamir Naveed Akram Nov 14, 2026 868 views

Question details

Combine values from multiple rows that match two criteria into a single Excel cell.

Combine Multiple Matching Values in One Excel Cell with TEXTJOIN
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the destination cell

On Sheet2, click the cell where you want the combined values to appear.

2
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,""),""))). Change the ranges and criteria references to match your worksheet.

3
Confirm the formula

Press Enter in Microsoft 365. In older Excel versions that support TEXTJOIN but require array entry, press Ctrl+Shift+Enter instead.

4
Check the result

Compare the destination cell with the matching rows on Sheet1. Values from column D should appear in row order, separated by commas.

Keep the ranges aligned: The criteria ranges and result range must cover the same rows. TEXTJOIN ignores empty results because its second argument is TRUE.
Work with matching values in WPS

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. 1. Open your workbook: Open WPS Spreadsheets and choose Open to select your Excel workbook.
  2. 2. Select an output cell: Click the cell where you want the matching values to appear.
  3. 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. 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.
Combine values that meet two criteria in one cellOpen and edit Microsoft Excel XLSX filesUse a familiar spreadsheet interfaceWork with formulas without creating a custom VBA function
microsoft office alternative - wps office

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.