Using value from 2 tables to fill a cell

  • Thread starter Thread starter pgruening
  • Start date Start date
P

pgruening

Any help would be appreciated, Thanks

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

For example below:

I enter the name (Test in example), Plan (AA in example), and statu
(Family in example). I would like a formula that would fill in th
Dental Premium and Extended Health Premium from the following tw
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.0
 
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 followin
helps...

Assumptions:

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

E1:G7 contains your table for Extended Health Coverage (first two row
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 famil
status, such as Family.

Hope this helps!
Any help would be appreciated, Thanks

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

For example below:

I enter the name (Test in example), Plan (AA in example), and statu
(Family in example). I would like a formula that would fill in th
Dental Premium and Extended Health Premium from the following tw
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.0
Click to expand...
 
Back
Top