SOLVED

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

Copper Contributor

# 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
3 Replies
best response confirmed by GmPasman (Copper Contributor)
Solution

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

@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)``

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

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

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

Thanks!  Works as I wanted.

Case closed now.