0

I've created a table (not using the table tool just using the cells) in Excel in one sheet.

In another sheet I have called the min/max function for that table to return the highest/lowest value.

However I also want to know what year that value corresponds to so I was wondering how this can be done? B38:B55 are the years and E38:E55 are the values that are being searched.

How can I retrieve the corresponding year value?

0

=INDEX($B$38:$B$55,MATCH(MIN($E$38:$E$55),$E$38:$E$55,))

=INDEX($B$38:$B$55,MATCH(MAX($E$38:$E$55),$E$38:$E$55,))

These formulas will give you the first year for the value found. The min or max might be repeated. Note if you are using another sheet then the ranges should be prepended with the sheet name, like Sheet1!$B$38:$B$55

Example of duplicates {2013;2014;2015;2016;2017;2018;2019;2020;2021;2022;2023;2024;2025;2026;2027;2028;2029;2030} {118;130;109;134;112;135;126;109;125;123;103;111;118;140;102;140;102;105} So the min gets 2027 even though 102 also is in 2029. Similarly max gets 2926 but 140 is also in 2028. Note: You can use conditional formatting to highlight the max's and min's. Regards Brian

  • Thank you so much! Works perfectly and I now know how the INDEX function works! – Will Dec 31 '15 at 17:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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