VLookup when I know there are multiple matches (and I need them all)

[TheRedCardinal]

I have a sheet (Sheet 1) with a column that contains numerical employee IDs. Each employee has a single row.

I have another sheet (Sheet 2) that lists those IDs and contains project supervisors. Some employees have more than one supervisor. Where this is the case, the employee is listed in multiple (successive) rows, with the same ID, but the name of the supervisor varying.

I need to create 4 columns (that is the maximum number of supervisors) in Sheet 1, and lookup the names of all possible supervisors, sometimes 1, sometimes as many as 4.

I have some success with this by doing the following:

1. Creating a "helper column" in sheet 2 that uses a simple rolling COUNTIF formula to add an appending -1, -2, -3 or -4 to the employee ID
2. Calling my column headers Supervisor 1, Supervisor 2 etc in Sheet 1
3. Using Vlookup to lookup [ EmployeeID & "-" & Right(ColumnHeader,1)] in Sheet 2

This is working find except that for presentation purposes, I need the primary supervisor, who is always listed first, in Supervisor 1 column, and unfortunately the way the COUNTIF cheat is working means that for mulitple supervisors, they are ranked in reverse order.

I have written VBA code to do this but the user is not keen on it and would prefer a formula for future use.

Can this be done?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[TheRedCardinal]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Done!

 

[Fluff]

Thanks for that.
How about something like

=TRANSPOSE(FILTER(Sheet1!B2:B100,Sheet1!A2:A100=A2))

 

[TheRedCardinal]

Not quite, but I think there must be an error in your formula (you don't mention sheet 2?)

For context, here are the data fields:

Sheet 1 - Employee ID in column A
Supervisor 1 First Name in G
Supervisor 1 Second Name in H
As above through to Supervisor 4 Last Name in Column N

Sheet 2 - Employee ID in column K
Supervisor First Name in column AA
Supervisor second name in Column AB

Thanks again for looking!

 

[Fluff]

As you gave no real information I just posted a very generic formula.

Can you post some sample data of what you have & expected results.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[TheRedCardinal]

Thanks Fluff - sorry I hope I didn't come across as rude and ungrateful! I was writing the OP without access to the database so I was keeping it vague.

Anyway....

Here's what it should look like:

HUMS PGRs for AR Log.xlsx
	A	B	C	D	E	F	G	H	I
1	Person ID	Supervisor 1 First Name	Supervisor 1 Last Name	Supervisor 2 First Name	Supervisor 2 Last Name	Supervisor 3 First Name	Supervisor 3 Last Name	Supervisor 4 First Name	Supervisor 4 Last Name
2	10079018	Mr	Fluff	Peter	Rabbit	Dr	Shrek	Vihrat	Kohli
3	10083293	Mr	Fluff	Mo	Salah
4	10083997	Charles	Dickens
5	10094649	Charles	Dickens
6	10120682	Super	Man	Bo	Jangles
7	10248826	James	Hetfield
Sample Employees

Cell Formulas
Range	Formula
B2:C2	B2	='Sample Data'!B2
D2:E2	D2	='Sample Data'!B3
F2:G2	F2	='Sample Data'!B4
H2:I2	H2	='Sample Data'!B5
B3:C3	B3	='Sample Data'!B6
D3:E3	D3	='Sample Data'!B7
B4:C6	B4	='Sample Data'!B8
D6:E6	D6	='Sample Data'!B11
B7:C7	B7	='Sample Data'!B12

And here is what the supervisor data looks like:

HUMS PGRs for AR Log.xlsx
	A	B	C
1	Person ID	Supervisor 1st Name	Supervisor 2nd Name
2	10079018	Mr	Fluff
3	10079018	Peter	Rabbit
4	10079018	Dr	Shrek
5	10079018	Vihrat	Kohli
6	10083293	Mr	Fluff
7	10083293	Mo	Salah
8	10083997	Charles	Dickens
9	10094649	Charles	Dickens
10	10120682	Super	Man
11	10120682	Bo	Jangles
12	10248826	James	Hetfield
Sample Data

Obviously these column headers are different but just to give you a feeling for the data.

 

[Fluff]

Thanks for that.
How about, for 365 only

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Person ID	Supervisor 1 First Name	Supervisor 1 Last Name	Supervisor 2 First Name	Supervisor 2 Last Name	Supervisor 3 First Name	Supervisor 3 Last Name	Supervisor 4 First Name	Supervisor 4 Last Name
2	10079018	Mr	Fluff	Peter	Rabbit	Dr	Shrek	Vihrat	Kohli
3	10083293	Mr	Fluff	Mo	Salah
4	10083997	Charles	Dickens
5	10094649	Charles	Dickens
6	10120682	Super	Man	Bo	Jangles
7	10248826	James	Hetfield
8
Sheet2

Cell Formulas
Range	Formula
B2:I2,B7:C7,B6:E6,B4:C5,B3:E3	B2	=TOROW(FILTER('Sample Data'!$B$2:$C$100,'Sample Data'!$A$2:$A$100=A2))
Dynamic array formulas.

 

[TheRedCardinal]

Hey fluff I am not sure how to use that?
This is what I've placed in G2 (which is the first name, of Supervisor 1):

=TOROW(FILTER(Data!$AA$2:$AB$2597,Data!$K$2:$K$2597=Students!A2))

But what do I do next sorry!?

 

[Fluff]

What happened when you did that?