lookup table with specific criterea

J

joala

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?
 
S

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
 
J

joala

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?
 
S

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top