Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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) |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell color based upon cell value | Excel Discussion (Misc queries) | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
Set default in auto fill options, I always want to copy cell, som. | Excel Discussion (Misc queries) | |||
Can I fill colour in a excel cell from a return from an "IF" test. | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |