0

I’m trying to find a MAX figure and the year/s it can be related to.

  • YEARS 2000-2010 (A1:J1)
  • DATA (A2:J2)
  • MAX of DATA is placed in A3

I can use INDEX/MATCH to give me the FIRST MAX YEAR, where it matches the MAX of DATA, but this MAX figure could appear in multiple years (e.g. 2002, 2005, 2009).

FIRST MAX YEAR in B3

I have a formula in C3 that is now trying to start another index match using (OFFSET, 0, B3) to make the search begin after the FIRST MAX YEAR, but it gives me the first year it encounters for some reason.

=INDEX(A1:J1,MATCH(A3,OFFSET(A2:J2,0,MATCH(B3, A1:J1,0))))

I’ve rounded my figures so they have one decimal point, and I know I have two matching MAX of DATA figures in my range A2:J2..

Update: An image to hopefully explain more clearly.

Finding years with Max value Any ideas?

Thanks Michael


ANSWER:

I followed Abdul's link in the comments below and found a formula that has met my requirements very well.

=IFERROR(INDEX(A1:K1,SMALL(IF(A2:K2=$A$3,COLUMN(A2:K2)-COLUMN(INDEX(A2:K2,1,1))+1),nth)),"")

The nth cell is number 2. So it's looking for the 2nd occurence of my A3 cell.

Hope this helps people. And Exceljet is a great site. Highly recomend.

get-nth-match-with-index-match

  • Array Formulas could save you a lot of headaches. Example: exceljet.net/formula/… – Abdul Oct 4 '17 at 11:53
  • would love a picture ;-) – mcalex Oct 4 '17 at 13:03
  • Good point. I'll see if I can add one. – RocketGoal Oct 4 '17 at 13:30
  • @Abdul Great! I followed you link and found another formula on that page that after a little bit tinkering answered my needs perfectly. Many thanks. I'm going to post my formula in my question and close it. – RocketGoal Oct 4 '17 at 16:21
2

You could do this with formulas entered only in B3, C3, etc. But I suggest adding a helper row (row 4) to make the formulas more readable.

In cell B4 enter:

=MATCH($A3,OFFSET($A2:$K2,0,A4),0)+A4

This returns 6 which is the column number of the first occurrence of 19.0 in row 2. Copy this formula to the right and you will get 8 and 11 followed by #N/A in the remaining cells. Now enter in cell B3:

=INDEX($A1:$K1,B4)

And copy the formula to the right. You will get the years on which the maximum vale was found: 2005, 2007 and 2010 followed by #N/A in the remaining cells.

If you don't want to see the #N/A values, you can use the IFERROR function to change them to blanks.

  • I've just followed a link from the first comment to an array formula, and after a bit more searching and some slight tinkering between ROW and COLUMN found a formula that worked well. Thanks for your input. Mike. – RocketGoal Oct 4 '17 at 16:20
  • See the simpler formula below. Seems to me that finding an array of row numbers and then using INDEX() (as the linked reference does) is taking the long way around the barn. :-) – Bandersnatch Oct 4 '17 at 16:27
2

I like Blackwood's answer, as it doesn't require an array formula. But if you can't abide the helper row, or need to do this with just one formula, you can use this in A4 and fill to the right:

 =IFERROR(SMALL(IF($A$2:$K$2=$A$3,$A$1:$K$1),COLUMN()),"")

It's an array formula, so it must be entered with CTRL Shift Enter, rather than just Enter.

Here's how this works. The IF() produces an array of the years where the data matches A3 (the Max of the data) and FALSE everywhere else:

{False;False;False;False;False;2005;False;2007;False;False;2010}

And then SMALL() selects the 1st, 2nd, 3rd, etc. smallest values from that array using the column number as the index. It checks only the numbers and ignores the "False" values in the array.

Finally, IFERROR() replaces any #N/A errors with a blank.

  • I like both your answers, and I'm not against helper columns. I'll try them both. – RocketGoal Oct 4 '17 at 16:31

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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