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

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

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

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

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

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