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

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

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

I have worked through some of this and changed a few things but i need the
formula to recognize all of the short named data into the total comapany name.


=INDEX($A$1:$B$960,MATCH(D3,$B$1:$B$960,0),MATCH($ E$1,$1:$1,-1))

yes i did remove the sheets out and this works but only on an exact match

--
Message posted via http://www.officekb.com

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