Nesting IF & Lookup function to retrieve multiple values

Hello! So..I am having an issue creating this complex formula.


See Attached Spreadsheet.

The 1st Sheet (Employee) Lists all the Employee Data
The 2nd Sheet (Dependents) Lists all the Employee's Dependents data
The 3rd Sheet (Merge) is where certain data from both Sheet 1 and Sheet 2 need to go. Sheet 3's only purpose will be to export it's data to autofill pdf's (this part you can ignore).

The issue is that I need to pull Data from sheet 2 to fill in the information in Sheet 3 columns F-S. (Dependent information).

So ...what I need is for my formula to do this: VLOOKUP the employee name from Sheet Merge & Match it with The employee name from Sheet Dependents. If a match is found, then pull the dependent name, DOB, SSN, Relation, & Address and place it in the appropriate columns in Sheet Merge.

I do not know how to do this if the employee has multiple dependents. I also do not know how to make the formula put the spouse in the spouse column and the child in the child columns in Sheet Merge.

As I do not know if this is even possible, I hope an excel expert can help me out.

All I have thusfar is VLOOKUP(C2,Dependents!A:S,2,FALSE)

I think you need to use a helper column in the Dependents sheet to make it easier to identify the records that need to be brought across. I'm just about to go out now, but I'll pick this up later on.

Pete

You can use this formula in G3 of the Dependents sheet:

=IF(A3="","-",A3&E3&IF(E3="Child",COUNTIFS(A$3:A3,A3,E$3:E3,E3),""))

and copy this down as far as you like (I've copied down to row 20 in the attached file, as can be seen by the hyphens - I've also coloured this column blue).

Then in the Merge sheet I've used this formula in F2:

=IFERROR(INDEX(Dependents!B:B,MATCH($A2&" "&$B2&"Spouse",Dependents!$G:$G,0)),"")

This can be copied across into G2:I2, although cell G2 will need to be formatted as a date, and in I2 you will need to change the column reference from E:E to F:F (this is how the B:B shown in red above changes when you copy the formula across). I'm not sure what column J is for, so I've left it blank and also inserted a new blank column O to separate the dependents.

In K2 you can use this formula:

=IFERROR(INDEX(Dependents!B:B,MATCH($A2&" "&$B2&"Child1",Dependents!$G:$G,0)),"")

which is very similar to the other formula, except for the item in red. Again, this can be copied across into L2:N2, with appropriate changes to the date format in L2 and changing the E:E to F:F in N2.

You can use this formula in P2:

=IFERROR(INDEX(Dependents!B:B,MATCH($A2&" "&$B2&"Child2",Dependents!$G:$G,0)),"")

and as you can see this is very similar, with just the change in blue. Copy across and make changes as detailed above.

Then you can copy the formulae in cells F2:S2 down as far as you need to (I've copied to row 6 in the example file).

Hope this helps.

Pete

This worked amazingly. I had no idea some of this could be done. You have been super helpful!

Glad to be able to help, and thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete