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.
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.
I followed Abdul's link in the comments below and found a formula that has met my requirements very well.
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.