I have a list of movies which I imported into Excel. There are IDs next to these names:
Sheet 1 (movies)
NAME ID1 ID2 etc.
Hell Below Zero /m/0cv3y9v /m/0cv3ntp
Spanking Field Trip /m/0b6_5_2 /m/0b71c5f
The Madagascar Penguins in a Christmas Caper /m/09scq5b /m/09scq55 /m/09scq5h /m/03jr51q /m/0cmxxtg /m/09scq5p
Jour de fête /m/0j_7cx /m/0bgbxtr /m/0j_7d6 /m/0bgbxt9 /m/0j_7dc /m/0bgbxtj /m/0j_7d1
Sheet 2 (actors)
NAME ID1 ID2 etc.
Madlaine Traverse /m/0cv3y9v m/0dh5yz8
Stevo Žigon /m/02vdbrv / /m/0cv3ntp /m/0fs7k_4 /m/0dkd6q6
Sorry for single rows running into margin. Both actor and movie sheets start with a NAME then ID, ID, ID (varying number of IDs for each row).
Now I want to find a match between the ID in sheet 1 (movies) with sheet 2 (actors) AND replace the ID being searched for BY the actor
In this case, the top entry for actors:
Hell Below Zero /m/0cv3y9v /m/0cv3ntp
would become:
Hell Below Zero Madlaine Traverse Stevo Žigon
/m/0cv3ntp would also have a match but I don't have it here.
I want to be able to do this for every ID in the movie sheet. Creating another sheet with the entries is fine... I just want to be able to get:
Movie: actor A, actor B etc. for each row.
I think it is VLOOKUP but after a few tries I can't work it out!
Thanks!
Bookmarks