Min value and IF

[Peter B]

I would like to find in a table the "record" that has the lowest value above a pre specified value. I.e. I have a table with 3 columns and variable number of rows. I would like to find a type of products (column A), and check which of this type of products that have the lowest date (MIN) above todays date (column B).

Does anyone have any clue how to solve this?

Peter B

 

[Frank Kabel]

Hi Peter
one way:
To get the lowest date of a specific product name above today enter the
following formula as array formula (CTRL+SHIFT+ENTER)
=MIN(IF((B1:B9999)*(A1:A9999="product_name")>=TODAY(),B1:B9999,""))
assuming the date is stored in column B

With this date you can now use VLOOKUP or INDEX/MATCH to get other
dates of this record

HTH
Frank