Using Dynamic Named Ranges in a Formula With INDEX, MATCH and MAX via a Dropdown Cell Containing the Range Name

[tsgnms]

Hi all!

I have tried to find a solution to this problem, but so far nothing I've found has either worked or explained why it can't - so I'm turning to you guys!

I have a standard data table from which I wish to display the maximum and minimum value of a given criteria using in-cell dropdowns. Due to the sensitive nature of the data I am unable to share the actual workbook or information, so I have created an example for this question using the premier league table:

Columns A:F contain the information, column I contains the dropdowns and desired return, and column J has an explanation of what I have in column I.

I have a series of dynamic named ranges:

I want users to be able to choose a Team in I2 and a metric (based on the headings) in I3 using in-cell dropdowns. The cells below would then show the value for that team, and then the team with the highest value and lowest value for the chosen metric. In the example, it shows Leicester's goals for and that Liverpool have the highest GF and Norwich the lowest. When I use the GF named range as shown in the example everything works perfectly and as expected. However, if I try to reference the contents of I3 in place of a GF in the MAX() part of I5 and I6 I get an error.

I've tried using indirect (despite my reservations in using that at all!) with no luck, so am hoping to either get a solution or for someone to say it simply isn't possible.

I hope I've explained that okay, please let me know if I've missed any important information.

Many thanks in advance for your help!

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also what was the formula you tried that didn't work?

 

[tsgnms]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also what was the formula you tried that didn't work?

I've updated my account as requested. Says I'm using Office 365 ProPlus desktop version 1808 on Windows 10.

The formula that doesn't work is:

=INDEX(DataTable,MATCH(MIN($I$3),$I$3,0),1)

Where $I$3 contains the name of a dynamic named range

 

[Fluff]

Thanks for updating you profile. The version number is a bit old, can you check if you have the Filter function?

 

[tsgnms]

Do you mean if I have the =FILTER() available?

I can only use =FILTERXML() by the looks of it

 

[Fluff]

Ok, thanks for that. As you are using an old version of xl, try

=INDEX(Datatable,MATCH(MAX(INDEX(Datatable,,MATCH($I$3,Headings,0))),INDEX(Datatable,,MATCH($I$3,Headings,0)),0),1)

If you install all available updates this would be simpler.

 

[tsgnms]

Thank you so much for your help, that seems to work perfectly.

If you don't mind, could you help me understand exactly what's going on in that formula? I'm struggling a little to follow it

 

[Fluff]

This part INDEX(Datatable,,MATCH($I$3,Headings,0))returns the entire column where I3 matches the header values (in this case col C) & the first time it's used the MAX function will return the largest number in the column.