Function Problems

How to Return Values from Multiple Yes/No Columns using Excel IF Formula

Natalie Taylor Oct 19, 2026 869 views

Question details

The user needs a formula to evaluate three separate single-choice columns (Yes/No) and return a combined list of corresponding activities (Training, BIA, Comms) if the value is 'Yes', or a default message if no columns contain 'Yes'.

How to Return Values from Multiple Yes or No Columns in Excel

Product: Excel
Device & OS: not provided
Scenario: Consolidating conditional responses from a project tracking or survey spreadsheet into a single cell with comma-separated values.
Observed behavior: The user successfully identified the logic but faced syntax errors when trying to implement the formula using complex structured table references with punctuation in the headers.

Before you start

Ensure your spreadsheet software supports dynamic array functions like TEXTJOIN and FILTER. These functions are available in Microsoft 365, Excel 2019 and later, as well as modern versions of WPS Spreadsheet.

Solution 1Recommended

Use TEXTJOIN combined with IF and COUNTIF functions

This method uses TEXTJOIN to combine text from multiple nested IF statements, while COUNTIF checks if any 'Yes' values exist across the target range to provide a default fallback message.

The TEXTJOIN function is ideal because it allows you to ignore empty strings generated by negative 'No' or 'Not yet scheduled' answers, seamlessly joining the positive results with a delimiter like a comma.

Select the target cell

Click on the cell where you want the final text string to appear (e.g., cell E2 next to your data columns B, C, and D).

Input the COUNTIF and TEXTJOIN formula

In the formula bar, type the following formula: `=IF(COUNTIF(B2:D2,"Yes")=0,"No Readiness activities",TEXTJOIN(", ",TRUE,IF(B2="Yes","Training",""),IF(C2="Yes","BIA",""),IF(D2="Yes","Comms","")))`

Apply and drag down

Press Enter to evaluate the formula. Then, click the bottom-right corner of the cell and drag the fill handle down to apply this formula to the rest of the rows in your dataset.

Use TEXTJOIN combined with IF and COUNTIF functions

Formula Breakdown: The TRUE argument in TEXTJOIN ensures that any blank results (returned when the condition is not 'Yes') are automatically ignored, preventing extra commas from appearing in your text.

Solution 2

Use the FILTER function with Array Constants

For modern versions of Excel or WPS Spreadsheet, combining FILTER with TEXTJOIN creates a shorter, cleaner formula without using multiple nested IFs.

Solution 3

Fix Syntax Errors with Excel Table Structured References

If you convert your data into an official Excel Table, column names with punctuation (like question marks or slashes) can cause formula syntax errors.

Easily Manage Complex Formulas in WPS Spreadsheet

You can effortlessly build, test, and apply advanced logical formulas like IF, TEXTJOIN, and FILTER using WPS Spreadsheet. It provides a familiar interface and guarantees seamless compatibility with your existing workbooks.

1. Open your workbook in WPS: Launch WPS Spreadsheet and open the file containing your Yes/No tracking columns.
2. Select the output cell: Click the cell where you want to consolidate the column data.
3. Insert the TEXTJOIN formula: Go to the Formula bar, paste the `=TEXTJOIN(..., IF(...))` formula detailed in the solutions, and update the cell references to match your sheet.
4. Apply across rows: Press Enter to evaluate, then use the fill handle (the small square at the bottom right of the cell) to drag the formula down to the rest of your list.

100% compatible with Microsoft Excel file formats (.xlsx) and standard functions.Natively supports modern dynamic array formulas like FILTER, TEXTJOIN, and UNIQUE.Features built-in formula auditing tools to help you identify and fix syntax errors quickly.Lightweight, fast, and free to download for personal and professional use.

microsoft office alternative - wps office

Frequently Asked Questions

What should I do if my version of Excel doesn't support the TEXTJOIN function?

If you are using an older version (like Excel 2013 or earlier) that lacks TEXTJOIN, you can use the traditional concatenation operator `&` with nested IFs. However, handling commas becomes complex. Upgrading to a modern alternative like WPS Office gives you free access to newer functions like TEXTJOIN.

Why does my formula show a #NAME? or #VALUE! error?

A #NAME? error usually means your software version does not recognize one of the functions (like FILTER or TEXTJOIN) or there is a typo in the structured table references. Ensure your headers don't contain unescaped punctuation marks.

Can I use this formula for more than three columns?

Yes. If using the IF/TEXTJOIN method, simply add more `IF(Cell="Yes","Value","")` statements inside the TEXTJOIN function. If using the FILTER method, expand your array constant to match the number of columns, like `{"Training","BIA","Comms","Other"}`, and expand the range criteria (e.g., `B2:E2="Yes"`).

Excel & Data Problems