Take variable data from a range to link to rows in an existing range

[Chappy]

Hi,
Help! I have 2 worksheets of employee data. The first has employee name, dob, start date, etc., with one row for each employee.
I would like to add the employee's health, life, and dental plans and costs to their row from the second worksheet.
However, my second worksheet has the employee insurance choices in multiple rows, if they have multiple choices, and the choices may vary (different plan, or no plan.) So the second table looks like this:

Employee      Plan            Cost
A              Blue Cross        $70
A              Dental            $15
A              Life Ins          $ 3
B              Harvard Pilg      $65
B              Life              $3

I can't use my old standby, LOOKUP, because I need to compare the employee name in my first table, and then extract their appropriate insurance choices from the second table, and post in that employee's row in the first table. Their selections may differ, or they may not have a selection in the category. Ideally:

Emp     DOB     Start Date     Health      Cost  Dental Cost       Life   Cost
B      xx/xx/xx  xx/xx/xx     Havard Pilg  $65      -        -     Life    $3

Thanks for any help you could provide!

 

[iferror]

It appears to me that you only need a lookup with two parameters: employee and insurance.

This is how i would do:
1) turn health, dental and life columns to validation combobox so that you'll easily have a list of insurances for a given employee
2) look up in first table for employee and insurance

As i do not have a sample worksheet, i'm assuming that

"second" is the sheetname you have second table in
and
"first" is the sheetname you have first table in
and
second table is sorted by employee


1)
a) create named range "employees" for all entries in "second" worksheet column A
b) select cell D2 in first worksheet and then force validation by list using this formula
=OFFSET(second!$A$2;MATCH(first!A2;employees;0)-2;1;COUNTIF(employees;first!A2);1)
c) same on columns F and H (same formula)

now, type an employee name in A2 an then select D2, you'll get a list of all insurances for that employee

2)
a)add an help-column in "second" where you have concatenation(employee,insurance)
b)use a concatenation of the right columns to fetch the data from second table

Not sure i made myself clear enought p

 

[Chappy]

OK, do I need to have a combobox to do this? Ultimately I need this file as a list, not dropdowns. When I tried to create a combobox, the online help is telling me I need to add about 75 lines of VBA code. I'll try it if it will produce a file with each item and cost of insurance going to the right of the employee information in the "first" table, but if it's going to give me dropdown selections, that is not going to help the ultimate recipient of this file.
I typed the formula you suggested into D2, but kept getting a formula error. Checked the formula 3X, so I'm wondering if not having the combobox first would cause that problem.
Thanks!
Chappy

 

[iferror]

No wait, the combobox comes along with the validation. you do not need to add a combobox yourself, when you add validation to column D you'll get a combobox in column D (and F and H) with the list of insurances for the employee in column A.

You know how to add a named range?
http://www.homeandlearn.co.uk/excel2007/excel2007s7p6.html

You do not have to enter the formula in cell D2, select column D2 and add validation
http://office.microsoft.com/en-us/excel-help/apply-data-validation-to-cells-HP010072600.aspx
http://www.contextures.com/xlDataVal01.html
Select List in Allow and paste the formula in Source