+ Reply to Thread
Results 1 to 7 of 7

How to Match up 2 columns against another 2 colums in another sheet for similarites / diff

  1. #1
    Registered User
    Join Date
    01-13-2020
    Location
    Edinburgh
    MS-Off Ver
    Microsoft 2016
    Posts
    11

    How to Match up 2 columns against another 2 colums in another sheet for similarites / diff

    Hello,

    Yesterday I was looking to match up a list of staff names and telephone numbers from 2016 against a newer staff list of names and telephone numbers from 2019 to show those who existed before and those who were not on the 2016 list.

    I was very kindly given the following formula which helped me greatly and did the job just as required:

    =IF(IFERROR(MATCH($K10&"|"&$L10,INDEX($E$10:$E$18&"|"&$F$10:$F$18,0),0)*0,1),"New","Existing")

    I would however be greatly appreciative if anyone would be able to advise me how i could reach the same outcome however have the existing staff list (2016 Staff List) in another sheet within the same work book.

    I have attached the example spreadsheet in the hope this may help.

    Thanks in advance,

    Zeus82
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,169

    Re: How to Match up 2 columns against another 2 colums in another sheet for similarites /

    As they are using the same cell references, you just need to put the sheet reference in front of these, i.e.in M10:

    =IF(IFERROR(MATCH($K10&"|"&$L10,INDEX('2016 Staff Members'!$E$10:$E$18&"|"&'2016 Staff Members'!$F$10:$F$18,0),0)*0,1),"New","Existing")

    Note that you need apostrophes either side of the sheet name, as you have spaces in the name.

    Copy this down as required.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    03-18-2014
    Location
    Singapore
    MS-Off Ver
    Excel 2016
    Posts
    59

    Re: How to Match up 2 columns against another 2 colums in another sheet for similarites /

    Attached

    or =IF(ISERROR(VLOOKUP(K10,'2016 Staff Members'!$E$9:$F$18,2,FALSE))=TRUE, "New", "Existing")
    Attached Files Attached Files
    Christopher Yap

  4. #4
    Registered User
    Join Date
    01-13-2020
    Location
    Edinburgh
    MS-Off Ver
    Microsoft 2016
    Posts
    11

    Re: How to Match up 2 columns against another 2 colums in another sheet for similarites /

    Hello Bluesky63,

    That is exactly what I was looking for so thank you so much!

    I knew that I had to name the sheet such as you would with a =VLOOKUP formula however I was unsure where to add this part.

    Thanks once again!!

    Kind regards,

    Zeus82

  5. #5
    Registered User
    Join Date
    01-13-2020
    Location
    Edinburgh
    MS-Off Ver
    Microsoft 2016
    Posts
    11

    Re: How to Match up 2 columns against another 2 colums in another sheet for similarites /

    Hello Pete_UK,

    Sorry I had meant to add your name in there too as it was your formula that I initially used.

    Thank you to both of you for your help with this.

    I have noted down both ways as I am trying to learn all the different ways to do such.

    Thanks again

    Zeus82

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,169

    Re: How to Match up 2 columns against another 2 colums in another sheet for similarites /

    Glad to help.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

  7. #7
    Registered User
    Join Date
    01-13-2020
    Location
    Edinburgh
    MS-Off Ver
    Microsoft 2016
    Posts
    11

    Re: How to Match up 2 columns against another 2 colums in another sheet for similarites /

    Hi Pete,

    Thanks for the advice, il be sure to do that 😁

    I'm using my phone now and can't see thread tools? I was able to mark the thread solved yesterday whilst using a computer but can't see it. On phone. Hmmm

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1