Add values from one sheet to another based on field IDs

kristos

New Member
Joined
Jul 23, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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_titlepost_contentimage_ID
title1content112345;23456;45678
title2content267890;45678
title3content345678

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:

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_titlepost_contentimage_IDimage_URL
title1content112345;23456;45678[/URL]
title2content267890;45678[/URL]
title3content345678

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.
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

KOKOSEK

Board Regular
Joined
Apr 8, 2019
Messages
226
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
ID 45678 is twice in second table. Once related to image2 and 2nd related to image5 but I understand that's typo.
 

kristos

New Member
Joined
Jul 23, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 8, 2019
Messages
226
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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_titlepost_content
image_ID​
image_URLhelper1helper2helper3
title1​
content1
23456;45678;12345​
image2.jpg;image3.jpg;image1.jpgimage2.jpgimage3.jpgimage1.jpg
title2​
content2
67890​
image4.jpgimage4.jpg
title3​
content3
99999;45678​
image5.jpg;image3.jpgimage5.jpgimage3.jpg
12345​
image1.jpg
23456​
image2.jpg
45678​
image3.jpg
67890​
image4.jpg
99999​
image5.jpg



A
B
C
D
E
F
G
post_titlepost_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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,371
Messages
5,595,784
Members
414,020
Latest member
Meghdad

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top