split formula of data not giving expected results need assistance

I have a worksheet where I split description of goods of column G in column in J based on column K,L,M and U as criteria column H which represents a code how I am having an issue with one of them starting with 1702.

the expected should read in in column J 6*20*70 CL instead showing blank

Can anyone assist in formula .

I have hide the other rows to show the the row I having trouble

I think the problem may be the Tariff No. beginning with 1 instead of 2???

Hi John

Putting one does get the results however dragging as the others starts with 2 give me a blank.

Yes, but i do not what the right check should be.

The formula is wrong and/or the coding number is wrong.

What I was meaning that all the data is correct in column J except as below which starts with 1 instead of 2

17029010 SYRUP

However I have to stick to your suggestion in post 2 for that type of reference .

Can you not check if it is 1 OR 2 ???

most of them are 2 only which is correct but additional coding introduced starting 1 which is the issue othwerwise need to think how this can be done with the existing formula.

This ....????

=IF(AND(OR(LEFT(H173)="1",LEFT(H173)="2"),LEFT(H173,4)<>"2008"),IF(I173="WINES",K173&"*"&L173&"*"&IFERROR(LOOKUP(1,-FIND({37.5;75;"5L";"2L";"1.5L";"1L";"3L";"5L"},G173),{"37.5CL";"75CL";"5L";"2L";"1.5L";"1L";"3L";"5L"}),"75CL"),IF(U173="*",M173&"*"&LOOKUP(9^9,0+RIGHT(LEFT(G173,LOOKUP(9^9,FIND("X",G173,ROW($1:$99)))-1),{1,2,3}))&"*",K173&"*"&L173&"*")&LOOKUP(1,-FIND({20;25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G173),{"20CL";"25CL";"30CL";"33CL";"50CL";"70CL";"75CL";"100CL";"1L";"1.5L";"1.5L";"2L"})),"")

On right track but it looking also products with G e.g biscuits , I highlighted it in red

it applies only drinking items that it is juices , wines , shandy , liqueur ,mixed juices , ice tea

Added sheet with expected results from your adjustments

Why does I2 have a specific test for WINES?

=IF(OR(I2={"juices","wines","beer","liqueur","mixed juices","ice tea"}),IF(AND(OR(LEFT(H2)="1",LEFT(H2)="2"),LEFT(H2,4)<>"2008"),IF(I2="WINES",K2&"*"&L2&"*"&IFERROR(LOOKUP(1,-FIND({37.5;75;"5L";"2L";"1.5L";"1L";"3L";"5L"},G2),{"37.5CL";"75CL";"5L";"2L";"1.5L";"1L";"3L";"5L"}),"75CL"),IF(U2="*",M2&"*"&LOOKUP(9^9,0+RIGHT(LEFT(G2,LOOKUP(9^9,FIND("X",G2,ROW($1:$99)))-1),{1,2,3}))&"*",K2&"*"&L2&"*")&LOOKUP(1,-FIND({20;25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G2),{"20CL";"25CL";"30CL";"33CL";"50CL";"70CL";"75CL";"100CL";"1L";"1.5L";"1.5L";"2L"})),""),"")

TRY

=IFERROR(IF(MATCH(I2,$AJ$2:$AJ$25,0),IF(AND(OR(LEFT(H2)="1",LEFT(H2)="2"),LEFT(H2,4)<>"2008"),IF(I2="WINES",K2&"*"&L2&"*"&IFERROR(LOOKUP(1,-FIND({37.5;75;"5L";"2L";"1.5L";"1L";"3L";"5L"},G2),{"37.5CL";"75CL";"5L";"2L";"1.5L";"1L";"3L";"5L"}),"75CL"),IF(U2="*",M2&"*"&LOOKUP(9^9,0+RIGHT(LEFT(G2,LOOKUP(9^9,FIND("X",G2,ROW($1:$99)))-1),{1,2,3}))&"*",K2&"*"&L2&"*")&LOOKUP(1,-FIND({20;25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G2),{"20CL";"25CL";"30CL";"33CL";"50CL";"70CL";"75CL";"100CL";"1L";"1.5L";"1.5L";"2L"})),""),""),"")

AJ2:AJ25 has list of valid products

Thank you lot John

tested works a charm, more easy than to enter manually in the formual