Here is an example of using the array formula process in Excel to determine the MIN number in a list:
I have a table called Produce with a list of fruit and their quantities. I’ve set up a data validation drop-down list of the fruit, and want a formula to give me the minimum quantity for that fruit from the list:
The key to this formula is that it is comparing a range of products and a range of quantities, creating a sub-list from that group and determining the minimum. That requires an array formula. An array formula is entered using Ctrl + Shift + Enter (CSE) not just Enter, and is identified by curly brackets around the formula:
If you run through the steps of the Evaluate Formula process on the Formulas tab of the ribbon, you will see how it goes through and ultimately determines which quantities match the selected fruit, and determines the minimum of those quantities:
So remember, when comparing multiple ranges to determine a filtered set of data that you want to evaluate, use the array process, not just a standard formula.