More help need with my double col lookup function

[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

 

[Domenic]

Try...

=INDEX($C$3:$C$8,MATCH(1,($A$3:$A$8=K19)*($B$3:$B$8=MAX(IF(($A$3:$A$8=K19
)*($B$3:$B$8<=N19),$B$3:$B$8))),0))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[KimberlyC]

Thank you so much!!!

This works much better..

Except, I tried entering:

AL for the state, 2/1/2001 for the date, and I got #NA for the answer

It should go to the line in the table AL - 1/1/2003 -10000 with the answer
being 10000

If I type in 3/1/04 for the date.. it gives me the correct answer..of 6000

What could be causing that?

 

[Domenic]

Thanks you so much!!!

You're very welcome!

This works much better..
Except, I tried entering
AL for the state, 2/1/2001 for the date, and I got #NA for the answer

That's because no date appears in Column B that is less than or equal to
2/1/2001 for that state. But we can change the formula so that it uses
the earliest date available for a state when such is the case. Try the
following formula...

=INDEX($C$3:$C$8,MATCH(1,($A$3:$A$8=K19)*($B$3:$B$8=MAX(IF(($A$3:$A$8=K19
)*($B$3:$B$8<=N19),$B$3:$B$8,MIN(IF($A$3:$A$8=K19,$B$3:$B$8))))),0))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[KimberlyC]

Hi again!!

I'm now trying to add an IF condition to the formula as there are three
different tables (all set up the same way with State, Eff date and Amt) that
the formula will need to refer to depending on what is entered in L19.

If "Officer" is entered in L19, then the formula below needs to reference
the (table) cells in that formula (A3:C8)..as it does.

If "Partner" is entered in L19, then the formula needs to reference a
different table (set up the same as the other table) located in cells F3:H8
.....where F3:F8 = States , G3:G8 = Eff Date, and H3:H8 = Amt

And Lastly, If "SP" is entered in L19, then the formula needs to reference a
third table (set up the same as the other two tables) located in cells K3:M8
.....where K3:K8 = States, L3:L8 = Eff Date, and M3:M8 = Amt

I've tried adding the If condition to the formula.. but I am not having much
luck..an Excel keeps giving me error in my formula message.

Thanks in Advance!!!

Kimberly

 

[Domenic]

Hi Kimberly,

Are the first two columns of every table identical?

 

[KimberlyC]

Yes.. they are the same... the third col is differnt in each one.

 

[Domenic]

Great! First, define the following references...

Insert > Name > Define

Name: Officer
Refers to: =Sheet1!$C$3:$C$8

Click on 'Add'

Name: Partner
Refers to: =Sheet1!$H$3:$H$8

Click on 'Add'

Name: SP
Refers to: =Sheet1!$M$3:$M$8

Click on 'Ok'

Then, use the following formula...

=INDEX(CHOOSE(MATCH(L19,{"Officer","Partner","SP"},0),Officer,Partner,SP)
,MATCH(1,($A$3:$A$8=K19)*($B$3:$B$8=MAX(IF(($A$3:$A$8=K19)*($B$3:$B$8<=N1
9),$B$3:$B$8,MIN(IF($A$3:$A$8=K19,$B$3:$B$8))))),0))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[KimberlyC]

Guess what.. I spoke to soon

the data is the same kind of data in first two columns of the tables...but
it is not the exact same..

There can be three different eff. dates and amts for one state in the first
table (making the one state have three entries) and only one eff date and
amt for a state (making the state have one entry in the table) in the 2nd
table....and this will change as we update the tables in the future.

 

[Domenic]

Okay, since the first two columns of each table are in fact not
identical, we'll change strategies, somewhat.

First, define the following references...

Insert > Name > Define

Name: Officer
Refers to: =Sheet1!$A$3:$C$8

Click 'Add'

Name: Partner
Refers to: =Sheet1!$F$3:$H$8

Click 'Add'

Name: SP
Refers to: =Sheet1!$K$3:$M$8

Click 'Ok'

Now, use the following formula...

=INDEX(INDIRECT(L19),MATCH(1,(INDEX(INDIRECT(L19),0,1)=K19)*(INDEX(INDIRE
CT(L19),0,2)=MAX(IF((INDEX(INDIRECT(L19),0,1)=K19)*(INDEX(INDIRECT(L19),0
,2)<=N19),INDEX(INDIRECT(L19),0,2),MIN(IF(INDEX(INDIRECT(L19),0,1)=K19,IN
DEX(INDIRECT(L19),0,2)))))),0),3)

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[KimberlyC]

THANK YOU SO MUCH Domenic!!!
It's perfect now!!

I really appreciate all your help!

Kimberly