Vlookup - Most recent date after today.

[dowdc]

I am using Excel 2010 and have a list of Item#'s on Sheet1 and on Sheet2 I have a production schedule. I am trying to figure out a formula (like a vlookup) that will look at the Item# in Sheet1 and return a date from Sheet2. The trouble I am having is that the Item# is repeated on the production schedule multiple times, and I only want it to return the more recent future date. For example Item# 00006 has a "Est DLVRY Date" of 5/21/11 and 6/11/11. I want it to return 6/11/11 since the other date is in the past and 6/11/11 is the next soonest date for that sku.

If the sku isn't on the production schedule at all, except for past dates, I want it return a "---"

I was trying to use an imbedded vlookup in an IF statement, but it kept returning the first date available.

I hope this makes sense. Thanks for all your help.

Sheet 1:

<a target='_blank' title='ImageShack - Image And Video Hosting' href='http://imageshack.us/photo/my-images/690/excelsheet.jpg/'><img src='http://img690.imageshack.us/img690/121/excelsheet.jpg' border='0'/></a>
Uploaded with <a target='_blank' href='http://imageshack.us'>ImageShack.us</a>



Sheet 2:

<a target='_blank' title='ImageShack - Image And Video Hosting' href='http://imageshack.us/photo/my-images/812/excelsheet2.jpg/'><img src='http://img812.imageshack.us/img812/7074/excelsheet2.jpg' border='0'/></a>
Uploaded with <a target='_blank' href='http://imageshack.us'>ImageShack.us</a>

 

[ThaiLove]

Sorry, just tried to modify it to the below

=MIN(IF((Sheet2!L$2:L$5>=TODAY())*(Sheet2!E$2:E$5=Sheet1!B2),Sheet2!F$2:F$5+0))

Any suggestions on returning text strings? I won't be using numbers...

thx

 

[RoryA]

If the data is sorted on column F, then you can get the minimum row number that matches and use that in an INDEX formula:

=INDEX(Sheet2!F$2:F$5,MIN(IF((Sheet2!L$2:L$5>=TODAY())*(Sheet2!E$2:E$5=Sheet1!B2),ROW(Sheet2!F$2:F$5))))

 

[ThaiLove]

Problem solved, thanks a lot guys!