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
--
szy

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

On Jan 28, 6:38*pm, l szym wrote:
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
--
szy

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

"l szym" wrote:

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
--
szy

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)

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"l szym" wrote:

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
--
szy

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


"Pete_UK" wrote:

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

On Jan 28, 6:38 pm, l szym wrote:
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
--
szy

Glad to hear it - thanks for feeding back.

Pete

On Jan 28, 8:46*pm, l szym wrote:
THANKS, you just saved me a ton of time. *this worked great!!
--
szy