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

[GmPasman]

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

[#2 · Accepted 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)

[#3]

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

[#4]

@Harun24HR 

Thanks!  Works as I wanted. 

Case closed now.