Vlookup or Match Formula Suggestion

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)

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 ]

YOU ROCK!!!!! THANKS MAN!

~Mark M.