Excel dropdown SMALL IF formula

You are currently viewing Excel dropdown SMALL IF formula

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.

Dataset

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:

Converting the source data into an Excel table

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

Using data validation to create dropdown list

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

Using data validation to create dropdown list

To find the bottom score, I construct my SMALL IF formula in G6, like shown below:

Constructing SMALL IF formula

Your dropdown SMALL IF formula is ready! I hope this post helped you.

Download Practice Workbook

Kunal

Hey, I am Kunal Aggarwal, the founder and author of Kunal Excel Tip based in New Delhi, India. I have completed my postgraduation from NIIT Limited, New Delhi in Excel course in 2019. Since then, I have been using and learning Microsoft Excel and started writing posts on the spreadsheet software here since March 2024.

Leave a Reply