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

1. 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.

Zeus82

2. 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. 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")

4. 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. Re: How to Match up 2 columns against another 2 colums in another sheet for similarites /

Hello Pete_UK,

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. Re: How to Match up 2 columns against another 2 colums in another sheet for similarites /

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

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

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