LOOKUP - I need to lookup a value where the lookup vector is in any order

Hi,

I am trying to return a value (result vector) from a range of values (lookup vector) that are not in ascending or descending order.

Please refer to the attached file:

The sheet lists the age group in 5 years in totals and % for suburb, city and country. I am trying to return the age group that is the highest percentage in the suburb of Rosebank under "SUMMARY".

The highest percentage in this case is the age group 20 - 24 at 35.1%. I have used the MAX function to return the highest age group percentage in the SUMMARY box. However when I attempt to use the LOOKUP function to return the age group of 20 - 24, it returns 85+ which is incorrect (RED BOX). Using the IF function returns the correct age group however this is tedious to write as I have a number of scenarios that need this function applied.

Appreciate your assistance with this.

Hi,

Great! Thank you