OK your upload is not really the same as your example.

THis 1 is tricky because some rows need to discard the left data, some discard the right data and others need to derop both.

This is what I have so far, it works on a lot of them...
I added a helper column on Lookup sheet in C...
=IF(LEFT(A63,3)="Pos",A63,LEFT(A63,30)) copied down

Then used this...
=INDEX(LookupTable!B:B,MATCH(IF(LEFT(F2,3)="Pos","*"&MID(F2,22,30)&"*",LEFT(F2,30)),LookupTable!C:C,0))
copied down.

It drops the errors from 184 to 49

Play around with the LEFT(A63,30)) and MID(F2,22,30) parts and see if you can narrow it down further

Of course, someone may could up with a proper sollution for this
Play around with the