would love a good answer!

Unfortunately, I don't think you'll get one. (although, I've been wrong
before!)

You have too many "fuzzy" variables for matching using a formula. This would
even be a difficult task using VBA.

My recommendation is to add every possible iteration of the variable to your
lookup table. That probably "bites", but there you go!


--
Biff
Microsoft Excel MVP


"DAustin" wrote in message
...
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!
:-)