In Excel, to find lowest 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 SMALL IF formula.
I have the following dataset. Now I want to find lowest 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 bottom score, I construct my SMALL IF formula in G6, like shown below:
Your dropdown SMALL IF formula is ready! I hope this post helped you.
