help on data lookup

  • Thread starter Thread starter l szym
  • Start date Start date
L

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
 
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
 
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
 
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)
 
Back
Top