find function

  • Thread starter Thread starter rollover99 via OfficeKB.com
  • Start date Start date
R

rollover99 via OfficeKB.com

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...
 
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))
 
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(A2,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)
 
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
 
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,ISNUMBER(SEARCH(A2,Sheet1!A$2:A$959)),0)))
 
Back
Top