matching two data sets, of different sizes where one is horizontal

[Avit]

Hi All,

Would really appreciate help on this one:
I have the following simplified table:

Date	Name 1	Name 2	Name 3	Name 4	Name 5
1/1/2020	John	Jack	Michael
1/2/2020	Jack	Tim	Jeff	Shawn
1/1/2020	Tim	Jeff
1/3/2020	David	John	Tim	Tom
1/2/2020	John	Michael
1/2/2020	Jim	Shawn
1/1/2020	David	David	Shawn
1/3/2020	Michael	Jack

I need to match each one of these people to their role on their team (let's say, "Manager", "Member" and "Other" based on a second vertical dataset. Let's also assume, that each of the people can be in any of the 3 roles for any given "shift". (but just on one role for that shift).
so, for example

Date	Name	Role
1/1/2020	Tim	Manager
1/1/2020	John	Member
1/1/2020	Jack	Manager
1/12020	Michael	Other
1/2/2020	John	Other
1/2/2020	Tom	Member
1/2/2020	Tim	Manager
1/3/2020	David	Member
1/3/2020	John	Member

.Let's also assume that each of the "jobs" (on the first table), might or might not have a manger (usually if there is no manager, than the only name that appears is an "other":
Please also note that the tables are not exactly equal (there is some data missing in the second table, that exists in the first).

I want to match the second table with the first one, so that I can create an additional table of the same size as the first (9x6), but instead of names, I will see the roles they play. so for example in the first row instead of the names John, Jack and Michael, I will see Member, Manager, Other

I appreciate any help on this one.

Thanks!!

 

[Avit]

Thanks again.
The problem is I am actually working on google sheet and not excel (I am not a fan, but it's not up to me), and am very limited in getting add-ons. So power query might not be feasible for me.
Is there a traditional way to do this? My initial assumption was some kind of mix of matches and indexes and maybe an array, which I just couldn't figure out. I in fact tried some kind of array myself but failed miserably...

 

[sandy666]

so your thread should be posted here: General Excel Discussion & Other Questions
and would be fine if you update your profile about Excel / Platform version

and I don't know any solution for GoogleSheet

 

[Avit]

Thank you, I will do that.
My assumption was that if there is a traditional (not power query) solution, it should also work for google sheets, as it does for excel.
I will of course try on the other forum.

And if there's anyone else who might be able to take a shot, I would be very very thankful.

 

[sandy666]

You are welcome

it should also work for google sheets, as it does for excel

not everything from Excel works on GoogleSheet
ask moderator via Report to move this thread to appropriate forum

 

[Peter_SSs]

I have moved the thread.
I also do not know Google sheets so cannot advise.
Also, expected results for one row is not very informative when expected results for 9 rows has been requested.
Remember that you are very familiar with your worksheet, data and requirements, but helpers here are not.

 

[Avit]

Thanks all,
Solved using array formula + index with two matches.