SOLVED

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

New 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?

 

 TheoGerardKarelPieterTheoPeter
Data A129575
Data B341168
CheckTRUETRUEFALSETRUEFALSEFALSE
       
#False3     
       
FALSEKarelTheoPeter   
3 Replies
best response confirmed by GmPasman (New Contributor)
Solution

@GmPasman Use FILTER() function.

 

=FILTER(B1:G1,B4:G4=FALSE)

 

Or below formula without having check row.

=FILTER(B1:G1,B2:G2+B3:G3>=8)

 

Harun24HR_0-1660629676824.png

 

@GmPasman 

=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.

index small.JPG

 

@Harun24HR 

 

Thanks!  Works as I wanted. 

 

Case closed now.