In Excel How to list all columns in a row that have a certain value?

Copper Contributor

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?


Data A129575
Data B341168
3 Replies
best response confirmed by GmPasman (Copper Contributor)

@GmPasman Use FILTER() function.




Or below formula without having check row.







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.

index small.JPG




Thanks!  Works as I wanted. 


Case closed now.