Lookup within multiple columns

[Cyphas]

I have a supplier who decided to populate data in a complex way thus making life a bit difficult in working with a form.

Item are placed like the below table.. this is just 1/8 of contents. I need to lookup in this sheet find the supplier code which is in either column A, C, E or G then return the relevant barcode number to the right of it.. so the value is in either columns B,D,F,H on the same row. Is there a VLOOKUP or equivalent formula to do this. I came across using some VLOOKUP and INDIRECT but couldnt figure out how to use in this complex instance as the main sheet I am using has a field supplier_code like 'J1' of which I need to ppopulate field sku_ean with the barcode from this sheet

JCB Barcode List Customer 02.12.21.xls
	A	B	C	D	E	F	G	H
2	AMAZON
3	J1	5055559602415	J7	5055559602477	J13	5055559610120	J19	5055559631316
4	J2	5055559602422	J8	5055559602484	J14	5055559610137	J20	5055559631323
5	J3	5055559602439	J9	5055559602491	J15	5055559610144	J21	5055559631330
6	J4	5055559602446	J10	5055559602507	J16	5055559610151	J22	5055559631347
7	J5	5055559602453	J11	5055559602514	J17	5055559612322	J23	5055559631354
8	J6	5055559602460	J12	5055559602521	J18	5055559612339
9
10
11	AZTEC
12	AL1	5055559602989	AL6	5055559603030	AL11	5055559603085	AL16	5055559605218
13	AL2	5055559602996	AL7	5055559603047	AL12	5055559603092	AL17	5055559605225
14	AL3	5055559603009	AL8	5055559603054	AL13	5055559605188	AL18	5055559610267
15	AL4	5055559603016	AL9	5055559603061	AL14	5055559605195	AL19	5055559610274
16	AL5	5055559603023	AL10	5055559603078	AL15	5055559605201	AL20	5055559610281
17	CRAFT COTTON		3PLY	5060019090312	BLEND 2	5060019095614	Toyball	5060019095621
18	ECRU	5060019092026
19	BLEACH	5060019092033
20	BABY 4PLY 100g
21	BY1	5060019090220	BY4	5060019090251	BY8	5060019090299	BY11	5055559610670
22	BY2	5060019090237	BY5	5060019090268	BY9	5060019090305	BY12	5055559610687
23	BY3	5060019090244	BY6	5060019090275	BY10	5055559610663
24	BABY 4PLY CONE
25	4BY1	5060019090411	4BY4	5060019090442	4BY8	5060019090480	4BY12	5055559624370
26	4BY2	5060019090428	4BY5	5060019090459	4BY9	5060019090497
27	4BY3	5060019090435	4BY6	5060019090466	4BY11	5055559624363
BARCODE MASTER

Thank you in advance for your help.

If Lookup is not ideal then is there a way to run a code to make populating in a sheet data into two columns
So I have all like

JCB Barcode List Customer 02.12.21.xls
	A	B
3	Supplier Code	EAN
4	J1	5055559602415
5	J2	5055559602422
6	J3	5055559602439
7	J4	5055559602446
8	J5	5055559602453
9	J6	5055559602460
BARCODE MASTER

 

[Cyphas]

The problem is you need to change all the references to A1 into A$1 before you copy it down, you also need the $ in the references to the array. so put this in K4 and copy it down:

=IFERROR(OFFSET(A$1,MATCH(J4,A$2:A$358,0),1),"")&IFERROR(OFFSET(A$1,MATCH(J4,C$2:C$358,0),3),"")&IFERROR(OFFSET(A$1,MATCH(J4,E$2:E$358,0),5),"")&IFERROR(OFFSET(A$1,MATCH(J4,G$2:G$358,0),7),"")

Ah, that worked now. Had to save file for codes to update and reflect values