# Excel Formula To Get First Non-Zero Value in Row and Return Column Header

I am attempting to find a formula where I can look at a column range within a row and return the column header in that row where the first non-zero value occurs, moving left to right.

Below is a screenshot of my data:

The results I would want to see in column H would be as follows, for each row:

``````9 = 10/31/2011
10 = 11/30/2011
11 = 11/30/2011
12 = 12/31/2011
``````

I have played around with some array formulas and searched through functions, but have not found any sucess yet. I am hoping another excel wizard may have an idea.

I want to avoid writing a UDF for now, if I can.

Sure, try this

`=INDEX(J\$8:N\$8,MATCH(TRUE,INDEX(J9:N9<>0,),0))`

• thanks for that. I found another way to do it, but yours is more elegant and clear :) – Scott Holtzman Nov 8 '13 at 17:55
• Is the second `INDEX` there to avoid entering as an array formula? – Felipe Dec 8 '14 at 17:31
• The first argument to INDEX: `J9:N9<>0`, two questions: 1) Which of the INDEX() forms is being used, the "Array form" or the "Reference form"? 2) What is the Excel word for this concept of comparing a range (?) i.e. J9:N9, to a single value (?) like 0 – The Red Pea Jun 22 '16 at 22:05
• @TheRedPea I'm not sure specifically what this is called, but it's an array formula in general. The `J8:N8` refers to the titles where the next index is looking for non-zero values, if that helps. – Raystafarian Jun 23 '16 at 11:45

I accepted the answer above but wanted to show the other way I found to do it, in case it helps anyone.

``````=OFFSET(\$I\$8,0,MATCH(0,J10:N10,1)+1)
``````

You could also write this as:

``````=INDEX(J\$8:N\$8,MATCH(0,J9:N9,1)+1)
``````

Well ! if the requirement is just placing values of J8 : N8 in column H, then Copy and transpose (Paste Option) should solve the issue.

Couldn't get the above to work, as the formula would return the largest column (number) that was non-zero. I used =MATCH(SMALL(J10:N10,1),J10:N10,0). The small ignores zero values.