7

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: enter image description here

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.

12

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
3

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

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

-1

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.