How to INDEX and MATCH to return value from another table (Excel 2007)

[cvincent]

I need some assistance please with a table lookup, wherein I need to lookup the value in column 1, and the value in column 5, and find the match in another table. I think this would be an INDEX and/or MATCH, but I can't seem to get it to work. Any assistance would be much appreciated.

Example:

Table 1 Col A Col B Col C Col D Col E Col F

Code Amt Class Type Payor
112 $1 Ins Y A
114 $5 Med W C


Table 2 Code Payor A Payor B Payor C Payor D Payor E
112 $20 $30 $25 $10 $15
114 $40 $50 $60 $35 $40


I need a formula in Table 1, Col F, that looks at the Code in Table 1, Col A, AND the Payor for that code in Col D, and returns the amount from Table 2 that matches that Code and Payor

i.e. In table 1, first row - Column F would return $20, second row would return $60.

Thank you.

 

[Weazel]

are you sure all the numbers in table 2 are numbers and not text ?
also see if there are any spaces after the letters in the payor criteria...

you could also try something like..

=SUMPRODUCT(($A$6:$A$7=A3)*(RIGHT($B$5:$F$5,1)=E3),$B$6:$F$7)

 

[cvincent]

Nothing seems to be working - I either receive a #N/A message, or a #REF message. What am I doing wrong?

 

[cvincent]

My header in Table 1 (Tab 1) is "Original Payor". The column lists all different payors, that are matched identical to the headers in Table 2 (Tab 2).

 

[Weazel]

my guess would be there may be an issue with how you have the data set up.
is what you posted your actual data or test data?

 

[Weazel]

can you post some of your original data ?

 

[cvincent]

This is an accurate representation of my data.


Tab 1 (Selected Data), Range K2:R12000

Svc Date Proc Code Ins Amt Pai Orig Class Cur Class Original Payor
01/04/2013 12345 -36.05 1 2 ALLIED
01/05/2013 67891 -36.05 1 1 EMERALD
01/26/2013 15945 -28.84 3 4 COMMERCIAL

Tab 2 (Rates), Range H4:N24

Original Payor ALLIED COVENTRY EMERALD PARAMOUNT COMMERCIAL GENERIC
12345 $17.45 $17.45 $18.32 $18.32 $18.32 $18.50
15945 $28.84 $28.84 $30.28 $30.28 $30.28 $30.57
67891 $42.75 $42.75 $44.89 $44.89 $44.89 $45.32

 

[Weazel]

ok, i think i got the columns right...


Excel 2010

	K	L	M	N	O	P	Q
ALLIED
EMERALD
COMMERCIAL

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFF00"]Svc Date[/TD]
[TD="bgcolor: #FFFF00"] Proc Code[/TD]
[TD="bgcolor: #FFFF00"]Ins Amt Pai[/TD]
[TD="bgcolor: #FFFF00"]Orig Class[/TD]
[TD="bgcolor: #FFFF00"]Cur Class[/TD]
[TD="bgcolor: #FFFF00"]Original Payor[/TD]
[TD="bgcolor: #FFFF00"]$$$[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1/4/2013[/TD]
[TD="align: right"]12345[/TD]
[TD="align: right"]-36.05[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]17.45[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1/5/2013[/TD]
[TD="align: right"]67891[/TD]
[TD="align: right"]-36.05[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]44.89[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1/26/2013[/TD]
[TD="align: right"]15945[/TD]
[TD="align: right"]-28.84[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]30.28[/TD]

</tbody>

Selected Data​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]Q2[/TH]
[TD="align: left"]=SUMPRODUCT((rates!$I$4:$N$4=P2)*(rates!$H$5:$H$7=L2),rates!$I$5:$N$7)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]Q3[/TH]
[TD="align: left"]=SUMPRODUCT((rates!$I$4:$N$4=P3)*(rates!$H$5:$H$7=L3),rates!$I$5:$N$7)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]Q4[/TH]
[TD="align: left"]=SUMPRODUCT((rates!$I$4:$N$4=P4)*(rates!$H$5:$H$7=L4),rates!$I$5:$N$7)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Excel 2010

	H	I	J	K	L	M	N
Original Payor	ALLIED	COVENTRY	EMERALD	PARAMOUNT	COMMERCIAL	GENERIC

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]12345[/TD]
[TD="align: right"]$17.45 [/TD]
[TD="align: right"]$17.45 [/TD]
[TD="align: right"]$18.32 [/TD]
[TD="align: right"]$18.32 [/TD]
[TD="align: right"]$18.32 [/TD]
[TD="align: right"]$18.50[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]15945[/TD]
[TD="align: right"]$28.84 [/TD]
[TD="align: right"]$28.84 [/TD]
[TD="align: right"]$30.28 [/TD]
[TD="align: right"]$30.28 [/TD]
[TD="align: right"]$30.28 [/TD]
[TD="align: right"]$30.57[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]67891[/TD]
[TD="align: right"]$42.75 [/TD]
[TD="align: right"]$42.75 [/TD]
[TD="align: right"]$44.89 [/TD]
[TD="align: right"]$44.89 [/TD]
[TD="align: right"]$44.89 [/TD]
[TD="align: right"]$45.32 [/TD]

</tbody>

rates​

 

[cvincent]

Thanks for all your help Weazel. I hadn't had a chance to thank you due to the overload of this file. I did, however, finally find the right formula using a vlookup: =VLOOKUP($R6,RATES!$A:$U,MATCH($L6,RATES!$1:$1,0),FALSE)
This formula looked at the code in column R, found it in the rate table, then matched column L to the header (payor) in the rate table, returning the correct rate for that code and for that payor company. I think I still have a few blond hairs left - not many though. Thanks again for helping me out on this.