lookup table with specific criterea
 

The following is an example of a spreadsheet with 3 columns.

Col 1 Col 2 Col 3
ABC .50 03/07/06
DEF .25 03/07/06
GHI .65 03/07/06
JKL .35 03/07/06
ABC .90 03/14/06
DEF 2.99 03/14/06
JKL 4.15 03/14/06

Here is another spreadsheet.

Col 1 Col 2
ABC .90
DEF 2.99
GHI n/a
JKL 4.15

How can I create a formula the bring in col 2 automatically from my first
spreadsheet based on the most current date from col 3 of my first spreadsheet?

lookup table with specific criterea
 

Try this in B14.

=IF(SUMPRODUCT(($A$1:$A$7=$A14)*($C$1:$C$7=MAX($C$ 1:$C$7))*$B$1:$B$7)=0,"N/A",SUMPRODUCT(($A$1:$A$7=$A14)*($C$1:$C$7=MAX($C$1 :$C$7))*$B$1:$B$7))

Where A14:A17 is your lookup values (ABC, DEF etc...). This will
return N/A if there is no data for the most recent date for the lookup
value. In your example it was GHI. This will work from one sheet to
another.

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=522852

lookup table with specific criterea
 

SteveG - you are awesome. This is exactly what I was looking for exept for
one more thing I just thought of. If my col 3 has a future date, how do I
change my formula to grap the latest date but not to exceed the current date?
Example: if I had another row showing "ABC" in Col 1, "5.55" in Col 2 and
"03/21/06" in Col 3, I would compare today's date to col 3 and not grab this
row. Are you with me?

"SteveG" wrote:


Try this in B14.

=IF(SUMPRODUCT(($A$1:$A$7=$A14)*($C$1:$C$7=MAX($C$ 1:$C$7))*$B$1:$B$7)=0,"N/A",SUMPRODUCT(($A$1:$A$7=$A14)*($C$1:$C$7=MAX($C$1 :$C$7))*$B$1:$B$7))

Where A14:A17 is your lookup values (ABC, DEF etc...). This will
return N/A if there is no data for the most recent date for the lookup
value. In your example it was GHI. This will work from one sheet to
another.

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=522852

lookup table with specific criterea
 

Thanks for the positive feedback.

This array formula should do it for you.

=IF(SUMPRODUCT(($A$1:$A$7=$A14)*($C$1:$C$7=MAX(IF( $C$1:$C$7<=TODAY(),$C$1:$C$7)))*$B$1:$B$7)=0,"N/A",SUMPRODUCT(($A$1:$A$7=$A14)*($C$1:$C$7=MAX(IF($ C$1:$C$7<=TODAY(),$C$1:$C$7)))*$B$1:$B$7))

When you are done typing the formula, commit with Ctrl-Shift-Enter
simultaneously rather than just Enter. That will put curly brackets {}
around the formula so it appears like this afterwards.

{=IF(SUMPRODUCT(($A$1:$A$7=$A14)*($C$1:$C$7=MAX(IF ($C$1:$C$7<=TODAY(),$C$1:$C$7)))*$B$1:$B$7)=0,"N/A",SUMPRODUCT(($A$1:$A$7=$A14)*($C$1:$C$7=MAX(IF($ C$1:$C$7<=TODAY(),$C$1:$C$7)))*$B$1:$B$7))}

Don't enter those yourself. This will pull in the data if the most
recent date is today but not greater than today. If you want less than
today just remove the "=" from the MAX(IF formulas.


HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=522852

lookup table with specific criterea
 

Awesome again. Just what I was looking for. Thanks..

"SteveG" wrote:


Thanks for the positive feedback.

This array formula should do it for you.

=IF(SUMPRODUCT(($A$1:$A$7=$A14)*($C$1:$C$7=MAX(IF( $C$1:$C$7<=TODAY(),$C$1:$C$7)))*$B$1:$B$7)=0,"N/A",SUMPRODUCT(($A$1:$A$7=$A14)*($C$1:$C$7=MAX(IF($ C$1:$C$7<=TODAY(),$C$1:$C$7)))*$B$1:$B$7))

When you are done typing the formula, commit with Ctrl-Shift-Enter
simultaneously rather than just Enter. That will put curly brackets {}
around the formula so it appears like this afterwards.

{=IF(SUMPRODUCT(($A$1:$A$7=$A14)*($C$1:$C$7=MAX(IF ($C$1:$C$7<=TODAY(),$C$1:$C$7)))*$B$1:$B$7)=0,"N/A",SUMPRODUCT(($A$1:$A$7=$A14)*($C$1:$C$7=MAX(IF($ C$1:$C$7<=TODAY(),$C$1:$C$7)))*$B$1:$B$7))}

Don't enter those yourself. This will pull in the data if the most
recent date is today but not greater than today. If you want less than
today just remove the "=" from the MAX(IF formulas.


HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=522852

lookup table with specific criterea
 

One more question for you, what does the "*"mean in this formula?

"joala" wrote:

Awesome again. Just what I was looking for. Thanks..

"SteveG" wrote:


Thanks for the positive feedback.

This array formula should do it for you.

=IF(SUMPRODUCT(($A$1:$A$7=$A14)*($C$1:$C$7=MAX(IF( $C$1:$C$7<=TODAY(),$C$1:$C$7)))*$B$1:$B$7)=0,"N/A",SUMPRODUCT(($A$1:$A$7=$A14)*($C$1:$C$7=MAX(IF($ C$1:$C$7<=TODAY(),$C$1:$C$7)))*$B$1:$B$7))

When you are done typing the formula, commit with Ctrl-Shift-Enter
simultaneously rather than just Enter. That will put curly brackets {}
around the formula so it appears like this afterwards.

{=IF(SUMPRODUCT(($A$1:$A$7=$A14)*($C$1:$C$7=MAX(IF ($C$1:$C$7<=TODAY(),$C$1:$C$7)))*$B$1:$B$7)=0,"N/A",SUMPRODUCT(($A$1:$A$7=$A14)*($C$1:$C$7=MAX(IF($ C$1:$C$7<=TODAY(),$C$1:$C$7)))*$B$1:$B$7))}

Don't enter those yourself. This will pull in the data if the most
recent date is today but not greater than today. If you want less than
today just remove the "=" from the MAX(IF formulas.


HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=522852

lookup table with specific criterea
 

It is the multiplier operator. To better understand how/why it works,
this site should answer all your questions. It has a lot of useful
info on SUMPRODUCT.

Cheers,

Steve



http://www.xldynamic.com/source/xld.SUMPRODUCT.html


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=522852