In Excel, to find highest numeric values with multiple criteria based on the selection of the dropdown lists, I will use an Excel table, data validation to create dropdown lists and the LARGE IF formula.
I have the following dataset. Now I want to find highest scores in G6 when the user makes a selection from the Group and Subject dropdowns in G2 and G3 respectively.
To accomplish the task, I perform the following steps:
I convert the source data into an Excel table and name it as data, like shown below:
By creating an Excel table, the formula will update automatically when new records are added or deleted in the source data.
I use data validation to create Group dropdown list in G2, listing unique groups in column B: A and B, by going to Data > Data Validation > Allow: List > Source: A,B
I use another data validation to create Subject dropdown list in G3, listing unique subjects in column C: English, Maths and Science, by going to Data > Data Validation > Allow: List > Source: English,Maths,Science
To find the top score, I construct my LARGE IF formula in G6, like shown below:
Your dropdown LARGE IF formula is ready! I hope this post helped you.
