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.