How to Return Results for Multiple True Conditions in Excel
Question details
The user needs a single Excel formula to evaluate multiple conditions and output a result for every condition that evaluates to true.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Evaluating complex data ranges where multiple criteria might be met simultaneously, requiring the extraction of all corresponding labels or values.
- Observed behavior
- Standard nested IF functions only return the value for the first true condition and immediately stop evaluating the rest of the conditions.
Ensure your version of Excel or your spreadsheet software supports dynamic array functions like FILTER and TEXTJOIN, which are highly recommended for extracting and combining multiple matches efficiently.
Use TEXTJOIN and FILTER Functions to Output All Matches
Combine TEXTJOIN and FILTER to extract an array of all matching results and display them as a comma-separated string in a single cell.
This is the most modern and efficient approach. The FILTER function will extract all values that meet your criteria, while TEXTJOIN compiles those extracted values into a single, clean text string.
Click on the cell where you want the combined true results to appear.
Type the formula starting with =TEXTJOIN(", ", TRUE, ) to specify a comma separator and instruct Excel to ignore any empty cells.
Inside the TEXTJOIN formula, add your FILTER function to test your multiple conditions. For example: FILTER(P2:X2, {S5,W5,AA5}>1, ""). Ensure the conditional array sizes align with your source data.
Press Enter. The complete formula (e.g., =TEXTJOIN(", ", TRUE, FILTER(P2:X2, {S5,W5,AA5}>1, ""))) will now evaluate all conditions and return every true match in one cell.
Concatenate Multiple IF Statements using the Ampersand (&)
For older spreadsheet versions that do not support the FILTER function, you can string individual IF statements together manually.
Easily Evaluate Complex Formulas with WPS Office
WPS Office fully supports advanced dynamic array functions like TEXTJOIN and FILTER. You can seamlessly evaluate multiple conditions and process complex data arrays exactly as you would in Microsoft Excel, completely for free.
- 1. Open your data in WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet document containing the complex data.
- 2. Input the array formula: Select an empty cell and type in your combined =TEXTJOIN(..., FILTER(...)) formula to evaluate your multiple conditions.
- 3. Execute the calculation: Press Enter to instantly display all matching true conditions in one consolidated cell.
Frequently Asked Questions
Why does my nested IF formula only return one result?
A standard nested IF function evaluates conditions sequentially and stops evaluating as soon as it encounters the very first TRUE condition. It is designed to return a single definitive output based on a prioritized sequence, not to aggregate multiple TRUE results.
Can I output the multiple true results into separate cells instead of combining them?
Yes. By using the FILTER function on its own (without wrapping it in TEXTJOIN) or by utilizing VSTACK/HSTACK functions, the spreadsheet will dynamically 'spill' each true result into adjacent empty cells rather than packing them into one.
What if my spreadsheet version doesn't support TEXTJOIN or FILTER?
In older software versions, you can concatenate multiple independent IF statements using the '&' symbol or the CONCATENATE function. This forces the software to evaluate each IF separately and string together all true outputs into a single text string.
