Vlookup or Match Formula Suggestion

[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!

 

[Mark W.]

=INDEX(Sheet2!A:B,MATCH("*"&SUBSTITUTE(Sheet1!A1," ","*")&"*",Sheet2!A:A,0),2)

 

[Guest]

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?

 

[Mark W.]

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

 

[Guest]

YOU ROCK!!!!! THANKS MAN!

~Mark M.