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.

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)


--
Biff
Microsoft Excel MVP


"neil" wrote in message
...
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.

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" wrote:

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)


--
Biff
Microsoft Excel MVP


"neil" wrote in message
...
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.

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)


--
Biff
Microsoft Excel MVP


"neil" wrote in message
...
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" wrote:

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)


--
Biff
Microsoft Excel MVP


"neil" wrote in message
...
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.