Excel MIN IF Array Formula

A few weeks ago Karine asked me if there was a way she could find the minimum value for a specific product from a large table of data.

For example, if we take this table of data, let’s say we wanted to find the lowest price for Apples.

Excel MIN IF

One way is to use an array formula like this:

=MIN(IF((A4:A19=F3)*(B4:B19),(B4:B19)))

Entered with CTRL+SHIFT+ENTER as this is an array formula.

And where F3 contained our criteria; ‘Apples’

Result = $4.00

Excel MAX IF

Alternatively Karine could find the highest sale price by replacing MIN with MAX:

=MAX(IF((A4:A19=F3)*(B4:B19),(B4:B19)))

Result = $6.00

DMIN - Another Way to Find MIN IF

Of course many people are wary of array formulas and for that reason I prefer to use simpler alternatives if they’re available.

Introducing Database Function DMIN.

DMIN Syntax

=DMIN(database,field,criteria)

Returns the smallest number in the field (column of records in the database that match the conditions you specify.

Database

– Our whole table including the row headers – A3:B19

Field

– The column heading containing the value  – “Price”

Criteria

– Cells H3 – our column name, and H4 – our criteria

DMAX Alternative

The DMAX function works the same way but instead…wait for it….it finds the maximum:

=DMAX(A3:B19,"Price",H3:H4)

=$6.00

The only downside to Database functions is the spreadsheet real estate the criteria occupies, but I don’t think this should be an issue since each worksheet has over 1 billion cells.

Thanks for your question Karine.