Add values from one sheet to another based on field IDs

[kristos]

Hello,

I have a workbook with data for an import to a content management system (website) - two source tabs. One tab consist of post content data, where there are post titles, post content and image IDs (a set of numbers identifying images assigned to this post). This is how the tab looks like:

post_title	post_content	image_ID
title1	content1	12345;23456;45678
title2	content2	67890;45678
title3	content3	45678

Then I have a second tab is the image reference - there are only two columns: one with the image_ID number and the other with the image URL address:

image_ID	image_URL
12345	http://www.domain.com/image1.jpg
23456	http://www.domain.com/image2.jpg
45678	http://www.domain.com/image3.jpg
67890	http://www.domain.com/image4.jpg
45678	http://www.domain.com/image5.jpg

Now I want a third result tab to show what's in my first post content tab, but in an extra column to show the image URL addresses that result from the image_ID numbers in the other column. Something like this:

post_title	post_content	image_ID	image_URL
title1	content1	12345;23456;45678	http://www.domain.com/image1.jpg;[U...m/image2.jpg;http://www.domain.com/image3.jpg [/URL]
title2	content2	67890;45678	http://www.domain.com/image4.jpg;[URL]http://www.domain.com/image3.jpg [/URL]
title3	content3	45678	http://www.domain.com/image5.jpg

As you can see the fourth column in the above table consist of the image_URLs that are the URLs matching the specific IDs from the image_ID column in the second table. How to do that? Any recommendations? Thx.

 

[KOKOSEK]

ID 45678 is twice in second table. Once related to image2 and 2nd related to image5 but I understand that's typo.

 

[kristos]

ID 45678 is twice in second table. Once related to image2 and 2nd related to image5 but I understand that's typo.

oh, yeah - my mistake. it's just an example with some random digits. of course each image_ID number is unique.

 

[KOKOSEK]

Maybe is a quicker way but this way is simple. Some additional helper column added to separate IDs (I've cut URL to make column narrower)

A​	B​	C​	D​	E​	F​	G​
post_title	post_content	image_ID​	image_URL	helper1	helper2	helper3
title1​	content1	23456;45678;12345​	image2.jpg;image3.jpg;image1.jpg	image2.jpg	image3.jpg	image1.jpg
title2​	content2	67890​	image4.jpg	image4.jpg
title3​	content3	99999;45678​	image5.jpg;image3.jpg	image5.jpg	image3.jpg
		​
12345​	image1.jpg	​
23456​	image2.jpg	​
45678​	image3.jpg	​
67890​	image4.jpg	​
99999​	image5.jpg	​

A​	B​	C​	D​	E​	F​	G​
post_title	post_content	image_ID​	image_URL	="helper"&COLUMN()-4	="helper"&COLUMN()-4	="helper"&COLUMN()-4
title1​	content1	23456;45678;12345​	=TEXTJOIN(";",1,E2:L2)	=IFERROR(VLOOKUP(TRIM(MID(SUBSTITUTE($C2,";",REPT(" ",LEN($C2))),(COLUMN(A3)-1)*LEN($C2)+1,LEN($C2)))*1,$A$6:$B$10,2,0),"")	=IFERROR(VLOOKUP(TRIM(MID(SUBSTITUTE($C2,";",REPT(" ",LEN($C2))),(COLUMN(B3)-1)*LEN($C2)+1,LEN($C2)))*1,$A$6:$B$10,2,0),"")	=IFERROR(VLOOKUP(TRIM(MID(SUBSTITUTE($C2,";",REPT(" ",LEN($C2))),(COLUMN(C3)-1)*LEN($C2)+1,LEN($C2)))*1,$A$6:$B$10,2,0),"")
title2​	content2	67890​	=TEXTJOIN(";",1,E3:L3)	=IFERROR(VLOOKUP(TRIM(MID(SUBSTITUTE($C3,";",REPT(" ",LEN($C3))),(COLUMN(A4)-1)*LEN($C3)+1,LEN($C3)))*1,$A$6:$B$10,2,0),"")	=IFERROR(VLOOKUP(TRIM(MID(SUBSTITUTE($C3,";",REPT(" ",LEN($C3))),(COLUMN(B4)-1)*LEN($C3)+1,LEN($C3)))*1,$A$6:$B$10,2,0),"")	=IFERROR(VLOOKUP(TRIM(MID(SUBSTITUTE($C3,";",REPT(" ",LEN($C3))),(COLUMN(C4)-1)*LEN($C3)+1,LEN($C3)))*1,$A$6:$B$10,2,0),"")
title3​	content3	99999;45678​	=TEXTJOIN(";",1,E4:L4)	=IFERROR(VLOOKUP(TRIM(MID(SUBSTITUTE($C4,";",REPT(" ",LEN($C4))),(COLUMN(A5)-1)*LEN($C4)+1,LEN($C4)))*1,$A$6:$B$10,2,0),"")	=IFERROR(VLOOKUP(TRIM(MID(SUBSTITUTE($C4,";",REPT(" ",LEN($C4))),(COLUMN(B5)-1)*LEN($C4)+1,LEN($C4)))*1,$A$6:$B$10,2,0),"")	=IFERROR(VLOOKUP(TRIM(MID(SUBSTITUTE($C4,";",REPT(" ",LEN($C4))),(COLUMN(C5)-1)*LEN($C4)+1,LEN($C4)))*1,$A$6:$B$10,2,0),"")
		​
12345​	image1.jpg	​
23456​	image2.jpg	​
45678​	image3.jpg	​
67890​	image4.jpg	​
99999​	image5.jpg	​