You are sure that after you typed or pasted the formula into the formula bar,
you hit Control+Shift+Enter. Also, FIND is case sensitive. If that is the
issue change FIND to SEARCH.

Two other things. You will get an error if the match is in a row past 887,
because Sheet1!B$2:B$887 does not have anything past row 887 and the Match
criteria is to Sheet1!A$2:A$959. Also, I noticed that if A2 is empty, the
formula will return the first value in the range. Apparently Search and Find
will return a match for an empty cell. So, I would check A2 to see if it's
blank before doing the lookup.

=IF(A2="","",INDEX(Sheet1!B$2:B$887,MATCH(TRUE,ISN UMBER(SEARCH(A2,Sheet1!A$2:A$959)),0)))


"rollover99 via OfficeKB.com" wrote:

The end result on sheet2 is:
Company name SellTo
abc 1234
xyz 4567

This is using the company name from sheet2, find it in sheet 1 and return the
SellTo number to the cell.

What I am getting now is an #N/A error. I know there are many that match.
They are both sorted A to Z.
Also can you define a name for the data say "Sheet1!A$2:A$959" is "t" and
place that instead?

I used
=INDEX(Sheet1!B$2:B$887,MATCH(TRUE,ISNUMBER(FIND(A 2,Sheet1!A$2:A$959)),0))
Sheet1!B$2:B$887 = (This is the nuber column)

Sheet1!A$2:A$959 = (This is the long name)
A2 = (This is the short name)

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