lookup table with specific criterea

[Guest]

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?

 

[SteveG]

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 wil
return N/A if there is no data for the most recent date for the looku
value. In your example it was GHI. This will work from one sheet t
another.

HTH

Stev

 

[Guest]

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]

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-Ente
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 mos
recent date is today but not greater than today. If you want less tha
today just remove the "=" from the MAX(IF formulas.


HTH

Stev

 

[Guest]

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

 

[Guest]

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

 

[SteveG]

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

Cheers,

Steve



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