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.

 

[Jubjab]

How did you plan to match the value "A" to "Payor A", or "C" to "Payor C" ?

I think you tried to simplify your example so much that a solution for this particular example might not work on your real data.

 

[Juglaz]

Give this a try...

=INDIRECT("Sheet2!"&ADDRESS(MATCH(A2,Sheet2!A:A,0),MATCH("Payor "&D2,Sheet2!1:1,0)))

 

[cvincent]

Thanks for taking the time to look at this. I guess that is what I am trying to figure out how to do. In line 1, I have a code 112, and the Payor is A. Another row might have a code 112, with a Payor D - the Payors for each code can vary. I therefore need to lookup in Table 2 how much Payor A pays for a Code 112. Another row might have a code 112, and the Payor as D.

 

[Jubjab]

Did Juglaz' solution work for you?

 

[Jubjab]

Another possibility without using INDIRECT

=INDEX(Table2!$A$2:$F$3,MATCH(A2,Table2!$A$2:$A$3,0),MATCH("Payor "&E2,Table2!$A$1:$F$1,0))

 

[Weazel]

maybe....


Excel 2012

	A	B	C	D	E	F
Ins	Y	A
Med	W	B

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FCE4D6"]Code[/TD]
[TD="bgcolor: #FCE4D6"]Amt[/TD]
[TD="bgcolor: #FCE4D6"]Class[/TD]
[TD="bgcolor: #FCE4D6"]Type[/TD]
[TD="bgcolor: #FCE4D6"]Payor[/TD]
[TD="bgcolor: #FCE4D6"]$ Cha-ching[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]$1[/TD]

[TD="bgcolor: #F8CBAD, align: right"] $ 20.00[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]114[/TD]
[TD="align: right"]$5[/TD]

[TD="bgcolor: #F8CBAD, align: right"] $ 50.00[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FCE4D6"]Code[/TD]
[TD="bgcolor: #FCE4D6"]Payor A[/TD]
[TD="bgcolor: #FCE4D6"]Payor B[/TD]
[TD="bgcolor: #FCE4D6"]Payor C[/TD]
[TD="bgcolor: #FCE4D6"]Payor D[/TD]
[TD="bgcolor: #FCE4D6"]Payor E[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]$20[/TD]
[TD="align: right"]$30[/TD]
[TD="align: right"]$25[/TD]
[TD="align: right"]$10[/TD]
[TD="align: right"]$15[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]114[/TD]
[TD="align: right"]$40[/TD]
[TD="align: right"]$50[/TD]
[TD="align: right"]$60[/TD]
[TD="align: right"]$35[/TD]
[TD="align: right"]$40[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]{=1*INDEX($B$6:$F$7,MATCH(A2,$A$6:$A$7,0),MATCH(E2,RIGHT($B$5:$F$5,1),0))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]F3[/TH]
[TD="align: left"]{=1*INDEX($B$6:$F$7,MATCH(A3,$A$6:$A$7,0),MATCH(E3,RIGHT($B$5:$F$5,1),0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[cvincent]

No, I am getting a #REF message.

 

[cvincent]

Weazel, I am geting a #N/A message with this. I double checked all my references, and they are correct. I entered with Ctrl+Shift+Enter, and the curly brackets surrounded the formula.

 

[Juglaz]

No, I am getting a #REF message.

Are your table 2 headers actually Payor and the payor name, while table 1 only shows the payor name? This is likely the cause of the error. If table 2 headers are identical to the table 1 payors, then use:

=INDIRECT("Sheet2!"&ADDRESS(MATCH(A2,Sheet2!A:A,0),MATCH(D2,Sheet2!1:1,0)))