How to Lookup First column that is not empty and Return Corresponding Column Header In an Excel block of cells

user_19346581 0
Viewed 115

I am trying to Lookup First column that is not empty and Return Corresponding Column Header In an Excel block of cells (I think called Array?)

For example:

In this picture, I want to look at all of the entries in B4:G9, and have my formula tell me the first column not to be empty is the column labeled as 1.

I have the following formula so far that seems to work, where I search each row, have each row return the column header that the first non-zero number shows up in, and then search the minimum of the returned column headers. However my actual data has more than 100 columns and 100 lines, so I am looking for something cleaner.

=MIN(
INDEX($B$3:$G$3,MATCH(TRUE,INDEX((B9:G9<>0),0),0)),
INDEX($B$3:$G$3,MATCH(TRUE,INDEX((B8:G8<>0),0),0)),
INDEX($B$3:$G$3,MATCH(TRUE,INDEX((B7:G7<>0),0),0)),
INDEX($B$3:$G$3,MATCH(TRUE,INDEX((B6:G6<>0),0),0)),
INDEX($B$3:$G$3,MATCH(TRUE,INDEX((B5:G5<>0),0),0)),
INDEX($B$3:$G$3,MATCH(TRUE,INDEX((B4:G4<>0),0),0)))
is-empty multidimensional-array excel-formula excel
1 Answers

This formula should work for you, adjust the $B$4:$G$9 to match your actual dataset:

=INDEX($3:$3,1,MIN(IF($B$4:$G$9<>0,COLUMN($B$4:$G$9),COLUMNS($3:$3))))

Note: Depending on your version of Excel, you may have to confirm the formula with CtrlShiftEnter instead of just Enter. That will force the formula to be calculated as an array. You'll know you've done it correctly when the formula is surrounded by curly braces {formula} (the braces aren't put in manually, they're put in automatically upon successful array entry via CtrlShiftEnter)

user_26654250
Related