Results 1 to 15 of 15
  1. #1
    BakerMan
    Guest

    Matching Cells (2000)

    I am trying to identify matches between two columns(see attached) the formula I found and converted won't work, please help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Matching Cells (2000)

    Your formula looks up the value from column F in the first column of the lookup range, i.e. in column B. That is the way VLOOKUP works - it always looks for a match in the first column. Also, VLOOKUP cannot do a lookup to the left. You can use a combination of MATCH and INDEX to accomplish what you want. Put the following formula in H2:

    =IF(ISERROR(INDEX($B$2:$B$7,MATCH(F2,$E$2:$E$7,0)) ),"Did Not Match",INDEX($B$2:$B$7,MATCH(F2,$E$2:$E$7,0)))

    and fill down. MATCH returns the index of the cell in E2:E7 that matches F2, INDEX looks up the corresponding cell in B2:B7.

  3. #3
    BakerMan
    Guest

    Re: Matching Cells (2000)

    Thanks that worked and I appreciate your explanation.

  4. #4
    BakerMan
    Guest

    Re: Matching Cells (2000)

    Regarding this same question, could a partial match be found? Say the entries in one column are similar and meant to mean the same value but are entered slightly different i.e. Steve S. vs Steven S.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Matching Cells (2000)

    You would have to have some routine to take the item to lookup and make variations of it to look for an exact match.

    Check out <post#=282837>post 282837</post#>, it has a couple different routines to look for a "near match". It would find a match if you set it for 5 letters since:
    Steve S. matches Steven S. The method would find a "near match" for all steven's and all steve's, it does not look at the "last name"

    If you separated the first/last names, you could create a routine to do a near match on the last, extracted the near-matches and then did a near match from this list. Or you could create a list of "variant names" and looked up using those.

    Hope this helps,
    Steve

  6. #6
    BakerMan
    Guest

    Re: Matching Cells (2000)

    Thanks, I'll play around with the code.

  7. #7
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Matching Cells (2000)

    I am trying to do something similar to this post. If the first three letters of E1 matches the first three characters in B1, then I want a "Yes" in F1. My second issue is the letters in B1 are Upper/Lower and the letters in E1 are uppercase. Here is what did not work: =IF(MATCH(B1,E1,3), "Yes", "No Match"). The wrong answer I am getting is #N/A, when in fact there is a match. Thank You.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Matching Cells (2000)

    MATCH is meant for comparing entire cells. If you want to match the first 3 characters of two E1 and B1, use this formula in F1:

    =IF(LEFT(E1,3)=LEFT(B1,3),"Yes","No Match")

    The comparison is not case sensitive.

  9. #9
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Matching Cells (2000)

    I used your exact formula and it seemed to work. However, several times the returned answer was, "No Match", but I checked and there is a match. Example: B1 = Fletcher, Sue F. and E1 = FLETCHER,S. This should be a match. Any suggestions? Thank you.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Matching Cells (2000)

    Could there be a space before the name in either of the cells? That would throw the comparison off. If this is the case, you can use this variation:

    <code>=IF(LEFT(TRIM(B1),3)=LEFT(TRIM(E1),3),"Yes", "No Match")</code>

    TRIM removes leading and trailing spaces (as well as multiple spaces within the text)

  11. #11
    BakerMan
    Guest

    Re: Matching Cells (2000)

    When I use this formula, which works well, why does the background of my cell turn green? This happens with any result the formula returns. thanks

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Matching Cells (2000)

    I would guess you have some formatting in the cell (explicit or conditional). Formatting copies with the cells.

    Steve

  13. #13
    BakerMan
    Guest

    Re: Matching Cells (2000)

    The first thing I checked was 'conditional formatting' but nothing is set.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Matching Cells (2000)

    Do you have code in the worksheet module, for instance a Worksheet_Change event procedure? There must be something, formulas NEVER change the formatting of a cell.

    If you can't find anything, you can attach the workbook (zipped if necessary) to a reply. Remove any sensitive data before doing so, or replace it with dummy values.

  15. #15
    BakerMan
    Guest

    Re: Matching Cells (2000)

    I double checked the formatting and set it to none, since then I haven't had the problem. I don't recall ever changing anything to make it green, but it was probably just me. Thanks

Posting Permissions

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