Looking up data in a table
 

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

One way

http://tinyurl.com/67eco



Regards,


Peo Sjoblom

"KimberlyC" wrote:

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