I have a complex Lookup question that I can't solve ... can anyone help!?
(without resorting to macros). It's almost like a reverse of normal Lookups...

My 'Data' worksheet contains hundreds of rows. For simplicity here, I've
just shown a few rows from one column (let's say the following is in cells
A1:A6):

Acct nbr.txt
Cust Name.xls
Notes.txt
All acct nbrs.txt
Cust nbr.xls
Ac number.txt


The 'Lookup' worksheet contains a 2 column table like this (again I've shown
only a few rows, so let's say the following is in cells A1:B4):

Acct n Account number
ac n Account number
cust Customer Name
cust nb Customer Number


What I need in cells B1:B6 of 'Data' is for it to check each value in column
A of 'Lookup', and if that value is WITHIN the value in column A of 'Data',
to display the corresponding value from column B of 'Lookup'. It should be
case insensitive, and also show an error if there are 2 matches. Examples
should help explain!... below is a snapshot of how the A1:B5 of 'Data' should
end up, based on the 'Lookup' data above:

Acct nbr.txt Account number
Cust Name.xls Customer Name
Notes.txt
All acct nbrs.txt Account number
Cust nbr.xls [Error]
Ac number.txt Account number


I.e. for most of these it found a single match, except Notes.txt (there was
no match), and Cust nbr.xls (returned an Error because in the 'Lookup' table,
both 'cust' and 'cust nb' were matches).

Hope it makes sense ... would love a good answer!
:-)