• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Matching Columns/Values and Inserting Blanks

evanlamarr88

evanlamarr88

New Member
Hello,

I need to match cells in two columns to one another- one with more data than the other - and create empty spaces in the second column where there are no matching values. How do I do this? I've tried formulas from another site, and they didn't work. You can see them in columns C & D in the file uploaded.

Thanks for your help!
 

Attachments

Luke M said:
Formula in C2:
=IF(COUNTIF($B$2:$B$14,A2),A2,"")

Copy down to C51, and you're done.
Click to expand...
Super great! Thank you so much @Luke M ! I do have a problem, however: that list was a sample list I made to protect real names, and I can't get the formula to work on the worksheet I really need it. One difference is that the worksheet I really need has an extra column in A with information related to the name.
I know it can be lengthy, but can you help me understand how the formula works so I can apply it successfully elsewhere? I changed the the As & Bs to Bs & Cs, and extended the range in the COUNTIF function to include all the names I want matched, but it just draws blanks.
 
Last edited:
Formula is looking at the range of smaller names, and comparing it to each item in bigger list. If item exists, show it. Otherwise, show blank.

=IF(COUNTIF(ShortList, FirstItemInBigList), FirstItemInBigList, "")
 
Hey guys! Figured out my problem. All the cells in the column with the master list of names had a leading space that I didn't notice - that's why the formula you gave, @Luke M , was pulling blanks. I fixed the leading spaces with a =TRIM formula and it works great! Thanks so much. @NARAYANK991 - adding the zero to the matching formula was great - thanks for the tip - but as you can see in the file below, using that method seems to require two formulas and also numbers the matches in a separate column, which is unnecessary and somewhat cluttered in this instance. Is there a cleaner way to use this function?
 

Attachments

You must log in or register to reply here.
Back
Top