Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Vlookup or Match Formula Suggestion

  1. #1
    Guest

    Default

    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!

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  3. #3
    Guest

    Default

    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?

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    Guest

    Default

    YOU ROCK!!!!! THANKS MAN!

    ~Mark M.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •