Better to try a different approach if you're looking for more than one
match against a description.

Enter a "*" in the top of sheet 2 for the return value when there is
no match.
On sheet 1 try filling down this array formula (ctrl+shift+enter to
execute):

=INDEX(Sheet2!$A:$A,LARGE(ROW(Sheet2!$A$1:$A$251)*
ISNUMBER(SEARCH(SUBSTITUTE(Sheet2!$A$1:$A$251,"-",""),
SUBSTITUTE($A1,"-","")))*(Sheet2!$A$1:$A$251<""),1))

Then repeat the formula in the next columns replacing the "1" in the
"large(...,1)" at the end of the formula by "2","3" etc.

This should give:

Sheet1
SCORPIO... 713005L 7115-0003 *
Genesis... 71420164 * *
13 FEM... 7115-0011 * *
.....

Sheet2
*
71420164
7115-0003
7115-0011
713005L
713005R
.....



On 2 Apr, 09:07, wrote:
Thanks Lori, that works for the first column, but i just get zeros if
i try and find a second ref.

Cheers.