TEXTJOIN Formula in Excel With FILTER: How It Works

Formulas & functions · Updated November 20, 2026

To join only the values that meet a condition, combine Excel’s TEXTJOIN and FILTER functions. With names in A2:A7 and statuses in B2:B7, this formula returns a comma-separated list of names marked Open: =TEXTJOIN(", ",TRUE,FILTER(A2:A7,B2:B7="Open","")). It produces Ana, Chen, Deepa when those are the Open records.

How TEXTJOIN and FILTER Work Together

FILTER selects matching values from a range; TEXTJOIN combines the selected values into one text string. Used together, they are useful when you want a compact summary in one cell rather than a separate filtered list spilling down the worksheet.

The example assumes A2:A7 contains names and B2:B7 contains each person’s status. The FILTER part checks each status against Open and returns the corresponding names. TEXTJOIN then puts a comma and a space between the results. The second TEXTJOIN argument, TRUE, tells Excel to ignore empty values. The final empty-string argument in FILTER tells Excel what to return when there are no matches, instead of producing a no-results error.

This is a formula-based filter: the condition is written into the formula. It is different from using the Filter button on the Data tab to hide rows. That distinction matters because TEXTJOIN with FILTER responds to its stated criteria, not automatically to which rows happen to be visible on screen.

Understanding the Formula Parts

TEXTJOIN has the structure TEXTJOIN(delimiter, ignore_empty, text1, ...). The delimiter is the text inserted between joined items. It can be a comma, a space, a line break, or another separator. The TRUE setting skips empty values, while the remaining argument or arguments supply the text to combine. In this example, the FILTER result is supplied as one array argument.

FILTER has the structure FILTER(array, include, [if_empty]). Array is the range to return from, and include is a matching set of TRUE or FALSE tests. Excel keeps entries where the corresponding test is TRUE. The optional if_empty value is returned when there are no matches. In the main formula, the source range and the status range have the same number of rows, so each name is tested against its own status.

The delimiter and the condition are independent choices. For example, changing the delimiter to a semicolon changes only the separator in the final string; it does not change which records qualify. Use a delimiter that makes the result easy to read and unlikely to be confused with characters already inside the values.

Building a Formula for Your Own Data

Start by identifying the values you want to combine and the range that determines which ones qualify. Keep those ranges aligned: if the names start in row 2 and end in row 50, the status test should also start in row 2 and end in row 50. Then choose a condition that can be checked for every row.

  1. Choose a cell outside the source data for the result.
  2. Put the range to return in FILTER’s first argument.
  3. Write a matching test in its second argument, such as a status range equal to Open.
  4. Add an if_empty result, commonly an empty string, to handle no matches.
  5. Wrap FILTER in TEXTJOIN and set the delimiter and empty-value behavior.

For a larger worksheet, Excel Tables can make ranges easier to read and extend automatically as rows are added. Suppose a table is named Orders and has columns named Item and Status. The following formula can be entered in a normal worksheet cell outside the table; it returns matching items in one cell: =TEXTJOIN(", ",TRUE,FILTER(Orders[Item],Orders[Status]="Open","")). The table must have those exact column headings, and its Status entries must use the value Open for the stated condition to match.

Filtering on More Than One Condition

You can combine logical tests inside FILTER’s include argument. Multiplying tests makes an AND condition: both comparisons must be TRUE for a row to qualify. The following example assumes items are in A2:A10, statuses in B2:B10, and regions in C2:C10. It joins items that are both Open and in the West region: =TEXTJOIN(", ",TRUE,FILTER(A2:A10,(B2:B10="Open")*(C2:C10="West"),"")).

Adding tests instead makes an OR condition: a row qualifies if either comparison is TRUE. With items in A2:A10 and their statuses in B2:B10, this formula joins items marked Open or Pending: =TEXTJOIN(", ",TRUE,FILTER(A2:A10,(B2:B10="Open")+(B2:B10="Pending"),"")). Parentheses help make each comparison clear. For more complicated criteria, build and check the include expression separately before nesting it inside TEXTJOIN.

Text comparisons such as these are generally not case-sensitive in standard Excel formulas, so Open and OPEN match the same way. If your criteria depend on exact capitalization or more specialized rules, use an appropriate case-sensitive test rather than assuming the equality check distinguishes letter case.

Empty Results, Blank Cells, and Errors

A no-match result is common when users change a dropdown, date, or status. Giving FILTER an if_empty value avoids the #CALC! error that can occur when the argument is omitted and no rows qualify. An empty string makes the final result appear blank. If a visible message is more helpful, use a phrase such as No matches in that argument; TEXTJOIN will return that phrase when FILTER finds nothing.

TRUE in TEXTJOIN’s second argument skips empty values, but it does not remove duplicates, trim spaces, or correct inconsistent spelling. For example, Open and Open followed by an accidental space are different criteria values. A result with unexpected gaps or missing items is often caused by inconsistent source data rather than by the joining function itself.

Errors in the source values or in the include calculation can also flow through the formula. Check that the two FILTER ranges have compatible dimensions and that every comparison refers to the intended cells. If the result is unexpectedly blank, confirm that at least one row meets the condition and that the condition text matches the data. If the formula displays as text instead of calculating, check that the cell is not formatted as Text and that the entry begins with an equals sign.

FILTER Function Versus Excel’s Worksheet Filter

People often ask whether this formula joins only the rows currently visible after applying a worksheet filter. Not by itself. The FILTER function uses the formula’s include test; it does not use the Data tab’s AutoFilter state as its criteria. Hiding rows manually or filtering a table on screen does not automatically change which records the formula evaluates.

If you want the joined list to reflect a particular status, region, or date, put that condition directly into FILTER. This keeps the logic explicit and reproducible. If instead you need to summarize only the rows currently visible after an interactive worksheet filter, use a method designed to detect visible rows, such as a helper column or a separate extraction step, and then join that resulting range. Do not assume that hiding a row is equivalent to excluding it from FILTER.

The distinction is useful in shared workbooks: a formula-based condition gives a consistent answer regardless of how another person has arranged or filtered the sheet. Conversely, a visible-row-only summary is tied to the worksheet’s display state and should be labeled clearly so readers know what it represents.

Version Availability and Alternatives

FILTER is available in Microsoft 365 and Excel 2021 or later, including supported newer releases. TEXTJOIN is available in Excel 2019 and later. Both functions are also available in current Microsoft 365 versions for Mac and Excel for the web, though interface details can differ. In regional settings that use semicolons as formula separators, Excel may expect semicolons instead of commas; the examples here use standard comma separators.

In Excel 2019, which has TEXTJOIN but not the FILTER function, a helper column can mark qualifying records and TEXTJOIN can combine that column. Suppose names are in A2:A7 and statuses in B2:B7. Enter this formula in C2 and fill it down through C7: =IF(B2="Open",A2,""). Then, with the helper results in C2:C7, use this formula in a separate cell: =TEXTJOIN(", ",TRUE,C2:C7). The helper formula returns a name for an Open row and an empty string otherwise. In versions earlier than Excel 2019, TEXTJOIN may not be available, so a different approach—such as a newer Excel version, Power Query, or manual consolidation—may be needed.

Common Formula Problems to Check

  • Wrong range sizes: The array and include ranges should cover corresponding rows. A name range with six rows paired with a status range with five rows can cause a calculation error.
  • Unexpected no match: Check spelling, leading or trailing spaces, and whether the criteria are in the column referenced by the formula.
  • Only one result appears: Make sure FILTER’s array argument is the full range, not a single cell, and verify that the condition returns multiple TRUE values where expected.
  • Formula is not recognized: Check the Excel version for FILTER support and confirm whether your regional settings require semicolon separators.
  • Visible rows are still included: Remember that a worksheet AutoFilter does not replace the explicit include condition in FILTER.

When troubleshooting a complex formula, temporarily test the FILTER portion in an empty area. Its spilled result shows exactly which values are being selected; once that looks right, wrap it in TEXTJOIN. In current dynamic-array Excel, a formula entered in a cell can spill its results into neighboring cells, so make sure the test area is clear.

Frequently Asked Questions

Can I use TEXTJOIN with FILTER in Excel?
Yes. In Microsoft 365 and Excel 2021 or later, place FILTER inside TEXTJOIN to select matching values and combine them into one cell. Include FILTER’s if_empty argument to handle cases where nothing matches.

How do I join values that meet two conditions?
Multiply the two TRUE/FALSE tests inside FILTER’s include argument for AND logic. Use addition between tests for OR logic. Ensure each test range aligns row by row with the values being returned.

Does TEXTJOIN with FILTER ignore hidden rows?
No. FILTER evaluates its formula criteria, not the worksheet’s hidden or AutoFiltered row state. Add the desired condition to the formula or use a separate visible-row method if the display filter itself must control the result.

How do I return a blank when no records match?
Set FILTER’s optional third argument to an empty string. When combined with TEXTJOIN, the result is blank rather than a no-match calculation error.

Why does Excel show #NAME? for FILTER?
The installed Excel version may not include FILTER, or the function name may be misspelled. FILTER requires Microsoft 365 or Excel 2021 or later. Excel 2019 can use TEXTJOIN but needs a helper-column approach instead of FILTER.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.