search if a string contains text matching items in a list

[neil]

I have a list of product codes on a sheet in column A and a ranges of cells
containing text that has the product code in amongst the text. I want to
extract from that text the text that matches any product in the array in
column A. I have tried using the find function as an array formula but it
will only work if it finds the text of the first cell in the range.

 

[T. Valko]

Assuming that each text string contains *only 1* product code. If a string
contains more than one, the formula will return a match on the *last* one
listed in column A.

Product codes in the range A2:A10

Text starting in cell F2.

Enter this formula in G2 and copy down as needed:

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

 

[neil]

Thanks for your help. It does seem to work. I wonder if you could explain
how it works. I can't unravel it.
Thanks a lot

 

[T. Valko]

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)