vlookup to a word in a cell

W

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
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top