Multiple Results with delimiter

[pto160]

I have a book report. One spreadsheet I maintain the product ID. Sometimes I need to pull this information for a specific order for a product ID and also the book description. Sometimes the customer would order one book and another customer would order 2 books or more. I have the colon ":" as the delimiter for the multiple book orders. Can this be done? I need the results to be in one cell, so I imagine you would have to use textjoin.

Book1
	A	B	C
2	Product	Result I would Like
3	1200	1200 [American History}
4	1200:1500	1200 [American History] 150050 [European History from 1600 to 1800]
5
6	Lookup Table
7	Product ID	Product Book
8	1200	American History
9	150050	European History from 1600 to 1800
Sheet1

 

[pto160]

I have a book report. One spreadsheet I maintain the product ID. Sometimes I need to pull this information for a specific order for a product ID and also the book description. Sometimes the customer would order one book and another customer would order 2 books or more. I have the colon ":" as the delimiter for the multiple book orders. Can this be done? I need the results to be in one cell, so I imagine you would have to use textjoin.

Book1
	A	B	C
2	Product	Result I would Like
3	1200	1200 [American History}
4	1200:150050	1200 [American History from 1900-2000] 150050 [European History from 1600 to 1800]
5
6	Lookup Table
7	Product ID	Product Book
8	1200	American History from 1900-2000
9	150050	European History from 1600 to 1800
Sheet1

 

[pto160]

Disregard the first post. The product ID for the second one is 150050.

 

[Peter_SSs]

Is this what you want?

24 03 05.xlsm
	A	B
1	Product	Result I would Like
2	1200	1200 [American History]
3	1200:150050	1200 [American History] 150050 [European History from 1600 to 1800]
4
5	Lookup Table
6	Product ID	Product Book
7	1200	American History
8	150050	European History from 1600 to 1800
pto160

Cell Formulas
Range	Formula
B2:B3	B2	=LET(p,TEXTSPLIT(A2,":"),TEXTJOIN(" ",,p&" ["&XLOOKUP(--p,A$7:A$8,B$7:B$8)&"]"))

 

[pto160]

Thank you so much. This solution works great. This will save me a lot of time.

 

[Peter_SSs]

Cheers. Thanks for the follow-up.