Return a Value by looking for a text string in entire column

[ckrxb]

Hi all-

Thanks in advance for anyone who can look at this! I have what I think is a pretty simple task that I cannot figure out. Our new system puts all of our barcodes into one cell for our products separated by a space. Our old one would put a separate row entry with repeating product names. I am just looking for a way to look through that cell with multiple barcodes, find it when presented on another sheet and return the accompanying product description. The amount of barcodes in each cell is dynamic as is the amount of items (currently around 17,500) since this report includes any new items each week

So sheet 1 would have

Description	Alias
COCA COLA 16OZ	4900005115
COCA COLA 2.0L	049000050103 496390
COCA COLA 20OZ	04904403 490440
COCA COLA 500ML(1)	049000024685 COCO50CALAA1001
COCA COLA CHERRY 20OZ	049000018011 4900004754

On sheet 2 I am trying to get column B to return sheet 1 column A if sheet 2 column A has any of the barcodes manually entered into it like below

490440	COCA COLA 20OZ
COCO50CALAA1001	COCA COLA 500ML(1)

I feel like I was getting close with isnumber(search formulas or the use of a helper column but I would get stuck with a spill error or n/a.

 

[DanteAmor]

Hi and welcome to MrExcel

Try:

varios 19jul2023.xlsm
	A	B
1
2	490440	COCA COLA 20OZ
3	COCO50CALAA1001	COCA COLA 500ML(1)
Sheet2

Cell Formulas
Range	Formula
B2:B3	B2	=LOOKUP(2,1/(ISNUMBER(SEARCH(" "&A2&" "," "&Sheet1!$B$2:$B$20000&" "))),Sheet1!$A$2:$A$20000)

Or in cell B2 on Sheet2 and copy down

=FILTER(Sheet1!A2:A20000,ISNUMBER(SEARCH(" "&A2&" "," "&Sheet1!B2:B20000&" ")))

Or simply in cell B2

=BYROW(A2:A3,LAMBDA(a,FILTER(Sheet1!A2:A20000,ISNUMBER(SEARCH(" "&a&" "," "&Sheet1!B2:B20000&" ")))))

 

[ckrxb]

That first solution looks to work perfectly! Thanks so much and for such a quick response!