Forum Discussion

GmPasman's avatar
GmPasman
Copper Contributor
Aug 16, 2022
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...
excel
Formulas and Functions
  • Harun24HR's avatar
    Harun24HR
    Aug 16, 2022

    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)

     

     

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Aug 16, 2022

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.

 

Resources