Excel formula to get the first three non-zero values and get the corresponding headers of them

Question

I have 12 columns with 0,1 inside. I have put them headers the name of months. I am trying to get the first three, 1 , from left to right and get the corresponding header in column 13, 14, 15 .

I started with the following but I get only the first header.

=INDEX(B$2:M$2;MATCH(TRUE;INDEX(B3:M3<>0;);0))

How can I do it? thank you

[This](https://stackoverflow.com/questions/21288782/excel-index-match-multiple-row-results) may be of use — cybernetic.nomad, Dec 21 '18 at 21:38

Scott Craner · Accepted Answer · 2018-12-21T21:49:42.497

3

Put this in N3 and copy over:

=INDEX($2:$2,AGGREGATE(15,7,COLUMN($B3:$M3)/($B3:$M3<>0),COLUMN(A:A)))

edited Dec 21 '18 at 21:49

answered Dec 21 '18 at 21:43

Scott Craner

148,073
10
49
81

Its working its working :) can you explain me how its work? – Maria Georgali Dec 21 '18 at 21:48
1

Did you change the `,` to `;` for your local settings? – Scott Craner Dec 21 '18 at 21:50
@MariaGeorgali it is for you. but for me it does not work with `;`. It only works with `,` – Scott Craner Dec 21 '18 at 21:51

Excel formula to get the first three non-zero values and get the corresponding headers of them

1 Answers1