Lookup Maximum Value Subject to Constraints

[ncalenti]

Ok, this has been killing me for a few days.
I have two tables
Number 1:
PK Date
A 1/1/2006
B 2/1/2009
C 3/1/2007
D 5/1/2007

Number 2:
PK Date
A 1/1/2005
A 2/1/2006
A 5/1/2009
B 2/1/2009
B 5/1/2010
C 5/1/2007
D 4/1/2007

So I want a column in table 1 that is the minimum date from table 2 that matches the PK and is >= the date in table 1.

It would return:
A 2/1/2006
B 2/1/2009
C 5/1/2007
D Err or NULL

I think that example kinda covers most of the possibilities here. If anyone has a good solution. Please help!

 

[T. Valko]

Ok, this has been killing me for a few days.
I have two tables
Number 1:
PK Date
A 1/1/2006
B 2/1/2009
C 3/1/2007
D 5/1/2007

Number 2:
PK Date
A 1/1/2005
A 2/1/2006
A 5/1/2009
B 2/1/2009
B 5/1/2010
C 5/1/2007
D 4/1/2007

So I want a column in table 1 that is the minimum date from table 2 that matches the PK and is >= the date in table 1.

It would return:
A 2/1/2006
B 2/1/2009
C 5/1/2007
D Err or NULL

I think that example kinda covers most of the possibilities here. If anyone has a good solution. Please help!

Let's assume table 1 is in the range A2:B5 and table 2 is in the range F2:G8.

Enter this array formula** in C2 and copy down as needed:

=IF(MIN(IF(F$2:F$8=A2,IF(G$2:G$8>=B2,G$2:G$8)))=0,"",MIN(IF(F$2:F$8=A2,IF(G$2:G$8>=B2,G$2:G$8))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Format as Date.

 

[T. Valko]

Let's assume table 1 is in the range A2:B5 and table 2 is in the range F2:G8.

Enter this array formula** in C2 and copy down as needed:

=IF(MIN(IF(F$2:F$8=A2,IF(G$2:G$8>=B2,G$2:G$8)))=0,"",MIN(IF(F$2:F$8=A2,IF(G$2:G$8>=B2,G$2:G$8))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Format as Date.

Or, you could use the shorter array formula**:

=MIN(IF(F$2:F$8=A2,IF(G$2:G$8>=B2,G$2:G$8)))

And then use a custom cell format to suppress the display of 0:

m/d/yyyy;General;;

Note that if using this formatting cells will still contain numeric 0 you just won't see it. The cell will appear to be blank but it's not.

 

[ncalenti]

Oh Man-
Thats awesome. Thanks so much for the quick help

 

[T. Valko]

Oh Man-
Thats awesome. Thanks so much for the quick help

You're welcome. Thanks for the feedback!