Vlookup or Match Formula Suggestion

G

Guest

Guest
how do i match cells from two worksheets of information without a precise cell name? Ex.:
SHEET 1 SHEET 2
GENERAL MOTORS GENERAL MOTORS INC

The vlookup fails, and removing the "false" tail on the vlookup comes back with the wrong information...GENERAL HOSPITAL.

HELP!
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Thanks for the reponse, but it didn't work. I see where you are going on this...a search, match and replace effort. I was aiming for more of a vlookup function...

=vlookup(a1,sheet2!a1:f5,2,false)

This works, but only when info in a1 and in the a column on sheet2 are precise. I'm looking for the smart version that would identify the company names that might be misspelled or have longer/shorter names. suggestions?
 
Upvote 0
On 2002-03-05 11:35, Anonymous wrote:
Thanks for the reponse, but it didn't work. I see where you are going on this...a search, match and replace effort. I was aiming for more of a vlookup function...

=vlookup(a1,sheet2!a1:f5,2,false)

This works, but only when info in a1 and in the a column on sheet2 are precise. I'm looking for the smart version that would identify the company names that might be misspelled or have longer/shorter names. suggestions?

=INDEX(Sheet2!$A$1:$F$5,MATCH("*"&SUBSTITUTE(Sheet1!A1," ","*")&"*",Sheet2!$A$1:$A$5,0),2)

MATCH returns the row number of the 1st wildcarded match. INDEX uses this row number to return the value in the 2nd column.
This message was edited by Mark W. on 2002-03-05 12:05
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top