Lookup help with splitting and the returning the value

[Jas9000d]

Afternoon 

Im a little lost, ive tried and tried and I cant figure it out 

So i have this formula that works fine 

ifs(
   [Store Logo]=Boxgrove,
      LOOKUP([Scanned Barcode],"Boxgrove API Product Data", "Barcode", "Name"),
   [Store Logo]=Slindon,
      LOOKUP([Scanned Barcode],"Slindon API Product Data", "Barcode", "Name"),
   [Store Logo]=Kirdford,
      LOOKUP([Scanned Barcode],"Kirdford API Product Data", "Barcode", "Name"),
   [Store Logo]=Warnham,
      LOOKUP([Scanned Barcode],"Warnham API Product Data", "Barcode", "Name"),
   [Store Logo]=Rogate,
      LOOKUP([Scanned Barcode],"Rogate API Product Data", "Barcode", "Name"))

however it has come to my attention that some products are multi barcoded. example layout 12345,67890 so when the barcode 12345 is scanned the lookup returns nothing. how can I get around this please 

Many thanks 

[AleksiAlkio]

One way is to write it like.. 
LOOKUP(INDEX(SPLIT([Scanned Barcode],","),1),"Boxgrove API Product Data", "Barcode", "Name"),

[Jas9000d]

I really appreciate your fast response, however this still returns nothing.

[AleksiAlkio]

To be sure it works properly, add [_THISROW] like..
LOOKUP(INDEX(SPLIT([_THISROW].[Scanned Barcode],","),1),"Boxgrove API Product Data", "Barcode", "Name")

And you are sure the correct syntax is comma separated value without anything else?

[Jas9000d]

Same problem, however looking at it this morning I believe ive been misunderstood

So the scanned barcode will only ever have 1 barcode its the barcode column in sheet Boxgrove API Product Data that will have multiple barcodes separated with a coma 

Sorry for any confusion, I hope this more makes sense? 

[AleksiAlkio]

Aaa.. okay. Then it could be something like..

ANY(SELECT(Boxgrove API Product Data[Name],INDEX(SPLIT([Barcode],","),1)=[_THISROW].[Scanned Barcode]))

[Jas9000d]

This one doesn't return any thing, even for ones with single barcodes 

[Jas9000d]

So the barcode I'm testing this on is 5023139215102. So this is the barcode I'm scanning into [Scanned Barcode] and in the barcode column is the value 5023139210107,5023139215102 and I need it to return the value in the [Name] column 

This needs to work when either barcode is scanned. 

I have setup a virtual column called Multi Barcode that splits the value in [Barcode] into a list, would this help or not? 

[AleksiAlkio]

The formula doesn't work properly because the 5023139215102 is your 2nd one in your string. Change the 1 to 2 in the formula.

[Jas9000d · Accepted solution]

I figured it out using 

ANY(
SELECT(
Boxgrove API Product Data[Name],
IN([_THISROW].[Scanned Barcode], [Multi Barcodes Boxgrove]

Thanks for all your help