1. 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. 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. Re: Matching Cells (2000)

Thanks that worked and I appreciate your explanation.

4. 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. 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. Re: Matching Cells (2000)

Thanks, I'll play around with the code.

7. 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. 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. 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. 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. 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. Re: Matching Cells (2000)

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

Steve

13. Re: Matching Cells (2000)

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

14. 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. 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
•