Forum Discussion
Solved
In Excel How to list all columns in a row that have a certain value?
I probably use the wrong search terms, but cannot find a solution for a (very simply) action I want to do in Excel.
I have a table with rows that consist of over 1000 cells (columns).
Per column I do calculations that determine if the data is PASS or FAIL.
So I have a row that only shows PASS or FAIL (or TRUE FALSE)
I can count all FAILS
But how do I get a row that shows the header of all fails, listed in order?
I like to list the first 10 FALSE results info
Simple example: (check is simple A+B<8)
How do I get Excel to fill in the red headers?
| Theo | Gerard | Karel | Pieter | Theo | Peter | |
| Data A | 1 | 2 | 9 | 5 | 7 | 5 |
| Data B | 3 | 4 | 1 | 1 | 6 | 8 |
| Check | TRUE | TRUE | FALSE | TRUE | FALSE | FALSE |
| #False | 3 | |||||
| FALSE | Karel | Theo | Peter |
=INDEX($B$1:$G$1,SMALL(IF($B$4:$G$4="FALSE",COLUMN($A:$F)),COLUMN(A:A)))An alternative could be this formula if you don't work with Office365 or 2021. The formula has to be entered with ctrl+shift+enter. In the example the formula is entered in cell B9 and copied across range B9:D9.
