Perhaps this formula (array entered - you must hit Control+Shift+Enter after
keying it into the formula bar)

Where Sheet1!A2:B4 contains the data table (abc company, etc), and A2
contains the short name you are trying to look up (abc). Adjust ranges as
needed.

=INDEX(Sheet1!B$2:B$4,MATCH(TRUE,ISNUMBER(FIND(A2, Sheet1!A$2:A$4)),0))

"rollover99 via OfficeKB.com" wrote:

I have sheet1 that has:

CompanyName SellTo
xzy company 1234
abc comany 4567

And sheet2 that has just company:

Company
xyz
rutter
abc

I am trying to do something like a vlookup but this is with names from the
second sheet that fit into the first sheet. I understand that I may be able
to use the find function but I am not familiar with it. What it is I am
trying to do is find all the words in the cell in sheet 2 that match sheet
1's, but they will not be exact because sheet 1 may have more text. Almost
like in access (Like "*" & 'xyz' &"*") but in this case it would be cell 'a1'
instead of xyz.

Any help or guidance would be appreciated...

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200607/1