vlookup to a word in a cell

[Wanna Learn]

Hi Scenario 2 workbooks with the same information . I need to do a
vlookup ( I think) to get the total number of orders for the company .
example workbook one has the name of the company as Arizona Chemicals Inc
THe other workbook has the name of the Company as Arizona Laboratories (they
are the same company) How can I do a vlookup so that these two match .
Maybe vlookup is the wrong function? All the names of the companies do not
exactly match in the two workbooks. thanks in advance Excel 2003

 

[Jacob Skaria]

Try the below in a fresh sheet before trying on your data. Suppose you have
data in Sheet2 A1:C5
Sheet2 A1 = Arizona Laoratories


Sheet1 A1 = Arizona Chemicals Inc

Try the below in Sheet1 B1
To return SHeet2. Col A
=INDEX(Sheet2!A1:C5,MATCH(LEFT("*" & A1,6)&"*",Sheet2!A1:A5,0),1)
To return SHeet2.B Col B
=INDEX(Sheet2!A1:C5,MATCH(LEFT("*" & A1,6)&"*",Sheet2!A1:A5,0),2)
To return SHeet2.C Col C
=INDEX(Sheet2!A1:C5,MATCH(LEFT("*" & A1,6)&"*",Sheet2!A1:A5,0),3)


The match function use a wild card with the first 6 characters in cell A1 like
MATCH(LEFT("*" & A1,6)&"*",Sheet2!A1:A5,0)

Try and adjust to suit your requirement

If this post helps click Yes