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

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.

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.

protected by Raystafarian Apr 26 '16 at 16:54

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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