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>

 

[dowdc]

Any ideas anyone? I would like to put the formula in cell J of Sheet 1.

 

[Jonmo1]

Welcome to the board..

Try this array formula that MUST be entered with CTRL + SHIFT + ENTER

=MIN(IF(('Sheet2'!L$2:L$100 > = TODAY())*('Sheet2'!E$2:E$100='Sheet1'!B2),'Sheet2'!E$2:E$100))


Hope that helps.

 

[dowdc]

For some reason Excel says there is an error in the formula and wants to change it to

=MIN(IF((Sheet2!L$2:L$100>TODAY())*(Sheet2!E$2:E$100=Sheet1!B2),Sheet2!E$2:E$100))


Thanks for your help!

 

[dowdc]

Still isn't working. Any other ideas?

Thank you

 

[Jonmo1]

Remove the spaces in the formula around the > = symbols.

I put those there to prevent the forum software from cutting off the formula because it thinks the > is an html code tag..


Also, make sure when you enter the formula, instead of pressing Enter,
Press CTRL + SHIFT + ENTER

When entered correctly, the formula will be enclosed in {brackets}

 

[ThaiLove]

I saw this post & it's the closest thing to what I want to do, so I created the excel sheet shown by dowdc to try & reproduce the result he wanted, but it's not working for me either

So sheet 2 has the same input as originally posted (except I modified a few of the dates to get it to work & shortened the range of rows to be more manageable). In Sheet 1 I put 00006 in cell B2 as part of the lookup value & a few variations of the below code which was put in J2 (with CTRL + SHIFT + ENTER)

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

below is the outcome of evaluate formula, keeps looking like it's going to work, but I just keep getting 0 as the outcome...

MIN(IF((Sheet2!L$2:L$5>=TODAY())*(Sheet2!E$2:E$5=Sheet1!B2),Sheet2!E$2:E$5))
MIN(IF(({41203;41207;41224;41224}>=41220)*(Sheet2!E$2:E$5=Sheet1!B2),Sheet2!E$2:E$5))
MIN(IF(({FALSE;FALSE;TRUE;TRUE})*(Sheet2!E$2:E$5=Sheet1!B2),Sheet2!E$2:E$5))
MIN(IF({FALSE;FALSE;TRUE;TRUE}*(Sheet2!E$2:E$5=Sheet1!B2),Sheet2!E$2:E$5))
MIN(IF({FALSE;FALSE;TRUE;TRUE}*({“00006”;”00007”;”00008”;”00006”}=”00006”),Sheet2!E$2:E$5))
MIN(IF({FALSE;FALSE;TRUE;TRUE}*({TRUE;FALSE;FALSE;TRUE}),Sheet2!E$2:E$5))
MIN(IF({FALSE;FALSE;TRUE;TRUE}*{TRUE;FALSE;FALSE;TRUE},Sheet2!E$2:E$5))
MIN(IF({0;0;0;1},Sheet2!E$2:E$5))
MIN({FALSE;FALSE;FALSE;”00006”})
0

or

MIN(IF((Sheet2!L$2:L$5>=TODAY())*(Sheet2!E$2:E$5=Sheet1!B2),Sheet2!F$2:F$5))
MIN(IF(({41203;41207;41224;41224}>=41220)*(Sheet2!E$2:E$5=Sheet1!B2),Sheet2!F$2:F$5))
MIN(IF(({FALSE;FALSE;TRUE;TRUE})*(Sheet2!E$2:E$5=Sheet1!B2),Sheet2!F$2:F$5))
MIN(IF({FALSE;FALSE;TRUE;TRUE}*(Sheet2!E$2:E$5=Sheet1!B2),Sheet2!F$2:F$5))
MIN(IF({FALSE;FALSE;TRUE;TRUE}*({“00006”;”00007”;”00008”;”00006”}=”00006”),Sheet2!F$2:F$5))
MIN(IF({FALSE;FALSE;TRUE;TRUE}*({TRUE;FALSE;FALSE;TRUE}),Sheet2!F$2:F$5))
MIN(IF({FALSE;FALSE;TRUE;TRUE}*{TRUE;FALSE;FALSE;TRUE},Sheet2!F$2:F$5))
MIN(IF({0;0;0;1},Sheet2!F$2:F$5))
MIN({FALSE;FALSE;FALSE;”chocolate”})
0

Anyone see what could be the problem?

Thanks!

 

[RoryA]

It looks like you are trying to find the lowest value in a column of Text, which won't work since MIN ignores text.

 

[Andrew Poulsom]

This worked for me:

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

 

[ThaiLove]

Thanks soo Much Andrew, much appreciated works great, now I just hope I can modify it to suit my needs

Cheers,

Gary