Using value from 2 tables to fill a cell
 

Any help would be appreciated, Thanks

I need to know the formula (without using VB) to have the Dental
Premium and Extended Health Premium taking from two charts/tables (I
can put these on the same worksheet or different worksheet within the
same workbook, I have no preference)

For example below:

I enter the name (Test in example), Plan (AA in example), and status
(Family in example). I would like a formula that would fill in the
Dental Premium and Extended Health Premium from the following two
charts/tables.

Name Test
Plan AA
Status Family
Dental Premium $47.00
Extended Health Premium $42.00

[Dental Coverage Extended Health Coverage
Plan Single Family Plan Single Family
AA $20.00 $47.00 AA $22.00 $42.00
BB $21.00 $48.00 BB $23.00 $43.00
CC $22.00 $49.00 CC $24.00 $44.00
DD $23.00 $50.00 DD $25.00 $45.00
E $24.00 $51.00 E $26.00 $46.00


--
pgruening
------------------------------------------------------------------------
pgruening's Profile: http://www.excelforum.com/member.php...o&userid=26011
View this thread: http://www.excelforum.com/showthread...hreadid=394693

I mocked up your data with the headers "Dental Plan, Single, Family" in
cells A2:C2 and the associated data in A3:C7. "Health Plan, Single,
Family" in cells E2:G2 and associated data in E3:G7. I used row labels
"Name, Plan, Status, Dental Premium, Health Premium" in A10:A14. The
formula for the dental premium in cell B13 is
=VLOOKUP(B11,$A$3:$C$7,MATCH(B12,$B$2:$C$2,0)+1,0)

The formula I used for the health premium in B14 is
=VLOOKUP(B11,$E$3:$G$7,MATCH(B12,$F$2:$G$2,0)+1,0)

I'm not sure how Name/Test comes into play, but see if the following
helps...

Assumptions:

A1:C7 contains your table for Dental Coverage (first two rows contain
your labels)

E1:G7 contains your table for Extended Health Coverage (first two rows
contain your labels)


Formulas:

Dental Premium...

=INDEX(B3:C7,MATCH(B12,A3:A7,0),MATCH(B13,B2:C2,0) )

Extended Health Premium...

=INDEX(F3:G7,MATCH(B12,E3:E7,0),MATCH(B13,F2:G2,0) )

...where B12 contains the plan, such as AA, and B13 contains the family
status, such as Family.

Hope this helps!

pgruening Wrote:
Any help would be appreciated, Thanks

I need to know the formula (without using VB) to have the Dental
Premium and Extended Health Premium taking from two charts/tables (I
can put these on the same worksheet or different worksheet within the
same workbook, I have no preference)

For example below:

I enter the name (Test in example), Plan (AA in example), and status
(Family in example). I would like a formula that would fill in the
Dental Premium and Extended Health Premium from the following two
charts/tables.

Name Test
Plan AA
Status Family
Dental Premium $47.00
Extended Health Premium $42.00

[Dental Coverage Extended Health Coverage
Plan Single Family Plan Single Family
AA $20.00 $47.00 AA $22.00 $42.00
BB $21.00 $48.00 BB $23.00 $43.00
CC $22.00 $49.00 CC $24.00 $44.00
DD $23.00 $50.00 DD $25.00 $45.00
E $24.00 $51.00 E $26.00 $46.00


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=394693

Thanks very much I'll try the suggestions

Peter


--
pgruening
------------------------------------------------------------------------
pgruening's Profile: http://www.excelforum.com/member.php...o&userid=26011
View this thread: http://www.excelforum.com/showthread...hreadid=394693