Combine VLOOKUP and MAX or MIN formula

**v!ctor** · 07-18-2012, 01:59 PM

Hello

I want to create a furmula that finds a specific value and select the min date from a range of cells of another column.

e.g In attached file - spreadsheet 2, column B find min date of data in spreadsheet 1

thank you
hope to be understandable

**BenMiller** · 07-18-2012, 02:05 PM

Can you post a non-XLSX version?

**v!ctor** · 07-18-2012, 02:13 PM

.xls file

**NBVC** · 07-18-2012, 02:17 PM

Try:

=MIN(IF(Φύλλο1!$A$2:$A$13=A2;Φύλλο1!$B$2:$B$13))

confirm it with CTRL+SHIFT+ENTER not just ENTER so you see { } brackets,, then copy down.

**v!ctor** · 07-21-2012, 05:22 AM

this formula works perfect...but i have one more question

if a cell is empty it calculates it as 0 and gives wrong result (date 0/1/1900). Is there any possibility for the formula to dont calculate blank cells and only the cells that have values?

thanks

**Palmetto** · 07-21-2012, 06:41 AM

Can you post a non-XLSX version?

You can open and edit .xlsx files if you install the Office Compatibility Pack. The only caveat is that Excel functions specific to version 2007+ will not work, nor can you edit Pivot Tables, but you still get most of the functions commonly used. Worth the install.

**NBVC** · 07-23-2012, 07:58 AM

Originally Posted by v!ctor

this formula works perfect...but i have one more question

if a cell is empty it calculates it as 0 and gives wrong result (date 0/1/1900). Is there any possibility for the formula to dont calculate blank cells and only the cells that have values?

thanks

Format the result cells to hide those.

Select the cells, go to format cells, they should be set as date, go to Custom and add ;;; to the end. (e.g. in English-US it would be m/d/yyyy;;