IF Statement & VLOOKUP

[TAngel]

I have two spreadsheets that I need to do a vlookup to bring values from one to the other. This I know how to do, however, I need an IF statement to ensure I bring over the right value when there are one to many.

Sheet 1 columns

A B C D E
EE SSN FullName Dep 1 Dep 2 Dep 3
111-11-1111 Smith, Joe

Sheet 2 columns

A B C
EE SSN Dep # Dep FullName
111-11-1111 1 Smith, Billy
111-11-1111 2 Smith, Jill
111-11-1111 3 Smith, Bob

I want my vlookup to put Smith, Billy is column C of sheet 1, Smith, Jill in column D of sheet 1 and Smith, Bob in column E of sheet 1.

I know I need a vlookup in each of the three columns of sheet one but don't know how to right the IF with the VLOOKUP.

TIA!

 

[SkipVought]

Hi,

You can use the OFFSET function to return the sub range related to the EE-SSN.

Then the INDEX Function to return the individual values. I don't know why you have your column B. Makes no sense...
[tt]
EE SSN Dep # Dep FullName
111-11-1111 1 Smith, Billy
111-11-1111 2 Smith, Jill
111-11-1111 3 Smith, Bob
222-22-2222 1 Skip
222-22-2222 2 Mary
222-22-2222 3 Amy
[/tt]
[tt]
EE SSN Dep 1 Dep 2 Dep 3
111-11-1111 Smith, Billy Smith, Jill Smith, Bob
222-22-2222 Skip Mary Amy
[/tt]
The formula in B2
[tt]
B2: =INDEX(OFFSET(Sheet2!$A$1,MATCH($A2,Sheet2!$A$2:$A$1000,0),2,COUNTIF(Sheet2!$A$2:$A$1000,$A2),1),COLUMN()-1,1)
[/tt]


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[TAngel]

Skip,
Can I get greedy for a minute? This worked well for me (thank you), however, there are some employees with up to 6 dep and others with only 1 - is there a way to add an additional forumla that leaves the field blank if it can't find a value instead of #ref?

 

[SkipVought]

Let it #REF! but use Format > Conditional Formatting to make those values "disappear" by shading the font the same shade as the background color.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[TAngel]

Skip, I am stuck again. Not fully understanding this formula I am unable to make the smallest change without your help.

I understand for this to work that I need to be in column B2, however, I need to put this in column S through X.

Thanks again for all your help.

Tee

 

[SkipVought]

It it ENTERED or PASTED into B2.

Then
Copy B2 and then
select ALL the cells you want the formula in then
Paste.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}