K
KimberlyC
Hi,
Below is my table of data
A B C
State Eff Date Amt
AL 1/1/2003 10000
AL 2/1/2004 6000
AL 4/1/2004 8000
CA 1/1/2002 20000
CA 1/1/2003 30000
CA 1/1/2005 15500
My Criteria is entered into cells:
K19 = "state"
N19 = "a date"
The answer (which is the amount in col C of the table) should go into cell
Q19
Here is the array formula I'm using in Q19
=INDEX($C$3:$C$7,MATCH(1,($A$3:$A$7=K19)*($B$3:$B$7=N19),0))
For Example: If my crieteria is:
K19= AL
N19 = 1/1/2003
Q19 =10000
This is working great..as long as the dates I entered into N19 match up
exactly to what is in the table in col B.
HOWEVER.... I now need to make the formula look at the State in K19 and then
look at the date that is entered into N19 (as it could be any date..and will
not match what is in the table for "eff. date") and select the amount that
is closest to that date (in N19) without selecting an amount that is
greater than N19.
For Eample:
If K19 = CA and N19 = 7/1/2004
Then Q19 = 30000 (It selected the amount for CA ,eff date of 1/1/03..which
is closest to 7/1/04 without selecting a date that was greater than 7/1/04)
I cannot figure out how to incorporate this into the formula above....
Any help is greatly appreicated..
Thanks in advance!!
Kimberly
Below is my table of data
A B C
State Eff Date Amt
AL 1/1/2003 10000
AL 2/1/2004 6000
AL 4/1/2004 8000
CA 1/1/2002 20000
CA 1/1/2003 30000
CA 1/1/2005 15500
My Criteria is entered into cells:
K19 = "state"
N19 = "a date"
The answer (which is the amount in col C of the table) should go into cell
Q19
Here is the array formula I'm using in Q19
=INDEX($C$3:$C$7,MATCH(1,($A$3:$A$7=K19)*($B$3:$B$7=N19),0))
For Example: If my crieteria is:
K19= AL
N19 = 1/1/2003
Q19 =10000
This is working great..as long as the dates I entered into N19 match up
exactly to what is in the table in col B.
HOWEVER.... I now need to make the formula look at the State in K19 and then
look at the date that is entered into N19 (as it could be any date..and will
not match what is in the table for "eff. date") and select the amount that
is closest to that date (in N19) without selecting an amount that is
greater than N19.
For Eample:
If K19 = CA and N19 = 7/1/2004
Then Q19 = 30000 (It selected the amount for CA ,eff date of 1/1/03..which
is closest to 7/1/04 without selecting a date that was greater than 7/1/04)
I cannot figure out how to incorporate this into the formula above....
Any help is greatly appreicated..
Thanks in advance!!
Kimberly