Let's use this sample data:

Product codes:

A2 = A101
A3 = A102
A4 = A103

Text string in F2: Code - A102 x113c

=LOOKUP(2,1/SEARCH(A$2:A$4,F2),A$2:A$4)

SEARCH returns the starting position of a substring within a string. The
substrings in this case are the product codes and the string is what's in
cell F2. If the substring isn't found then the result is a #VALUE! error.
So, the result of SEARCH(A2:A4,F2) is:

A101 = #VALUE!
A102 = 8
A103 = #VALUE!

This is what the formula would look like at this point:

=LOOKUP(2,1/{#VALUE!;8;#VALUE!},A$2:A$4)

The way that LOOKUP works is if the lookup_value is greater than all the
values in the lookup_vector it will match the *last* value in the
lookup_vector that is less than the lookup_value.

Our lookup_value is 2. The lookup_vector at this point is
{#VALUE!;8;#VALUE!}. Obviously, the lookup_value is not greater than any
number in the lookup_vector. So we divide the lookup_vector by 1 and we get:

1 / #VALUE! = #VALUE!
1 / 8 = 0.125
1 / #VALUE! = #VALUE!

=LOOKUP(2,{#VALUE!;0.125;#VALUE!},A$2:A$4)

Now our lookup_value is greater than any number in the lookup_vector. So,
the result of the formula is the value from the result_vector that
corresponds to the *last* number in the lookup_vector that is less than the
lookup_value. The *last* number in the lookup_vector that is less than the
lookup_value is 0.125.

The result_vector is the range of product codes in A2:A4 -

...Lookup_vector..........Result_vector
......#VALUE!..................A101
......0.125.........................A102
.....#VALUE!...................A103

So, the result of the formula is A102

You could also use an arbitrary big number as the lookup_value that is
guaranteed to be greater than any number in the lookup_vector and eliminate
the extra step of dividing the lookup_vector by 1:

=LOOKUP(1E100,SEARCH(A$2:A$4,F2),A$2:A$4)

However, this approach won't work when the lookup_vector is a logical test
that returns boolean TRUE or FALSE that are coerced to 1 and 0.

=LOOKUP(1E100,--(A$2:A$4="A102"),B$2:B$4)

The syntax I used is basically "generic" and will work in just about all
situations:

=LOOKUP(2,1/SEARCH(A$2:A$4,F2),A$2:A$4)