help on data lookup

[l szym]

cpt medicare aetna
70417 143.00 145.00
70418 200.00 250.00
70419 300.00 350.00

I need a formula that will give the result of 200.00 by defining cpt =70418
for medicare or 70419 & aetna gives me 350.00 as the result

 

[Pete_UK]

Suppose you use D1 for the column heading (medicare) and E1 for the
value of cpt (70418). Put this formula in F1:

=INDEX($B$2:$C$4,MATCH(E1,$A$2:$A$4,0),MATCH(D1,$B$1:$C$1,0))

Adjust your ranges to suit your real table. Change the values in D1
and E1 for other returns.

Hope this helps.

Pete

 

[Mike H]

Hi,

try this

=INDEX(A1:C4,MATCH(70418,A1:A4,0),MATCH("aetna",A1:C1,0))

In practice i'd use cell references to hold the lookup values

Mike

 

[Shane Devenshire]

Hi,

Assume your table starts in A1 with titles on the first row and you enter
70418 in E1 and medi in F1

=SUMPRODUCT((A2:A4=E1)*(B1:C1=F1)*B2:C4)

 

[l szym]

THANKS, you just saved me a ton of time. this worked great!!

 

[Pete_UK]

Glad to hear it - thanks for feeding back.

Pete