CODEWITHSUNDEEP
excelmultidimensional-arrayexcel-formulais-empty
Tesia Kellar
Tesia Kellar

Reputation: 11

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

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

Upvotes: 1

Views: 967

Answers (1)

tigeravatar
tigeravatar

Reputation: 26650

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)

Upvotes: 3

Related Questions