Looking up data in a table

[KimberlyC]

Hi

I have a table in worksheet 2 of my activeworkbook.

The table has data in cells A2:C500
Table consists of:
Col A lists States (certain states are listed 2, 3 and 4 times), Col B lists
Effective Dates, Col C lists Total

On Worksheet 1, the user enters a state in cell K16, and a date in cell
L16,.... then in cell M16, I need to enter a formula
that looks at the table in worksheet 2 and finds the same state in col A
that was entered in K16 and the date next to the state in Col B may NOT be
greater than the date that was entered in L16 of worksheet 1....when it
finds a match based on that ... it pulls that total into M16.

For ex:
The Table on Worsheet 2 looks like this

State Eff Date Total
AL 1/1/2004 50000
AR 1/1/2004 10000
CA 2/1/2003 700000
CA 1/1/2005 20000
DE 1/1/2005 40000




On Worsheet 1, if ...
K16 = CA and L16 = 6/1/04, Then M16 = 700000 (pulling it from the table)

I'm not sure what formula would work best in M16 to accomplish this...

Any help is greatly appreciated!

Thanks in advance,
Kimberly

 

[Peo Sjoblom]

One way

http://tinyurl.com/67eco



Regards,


Peo Sjoblom