How can I check to see if the 900 people appear in an Excel sheet that contains 16000 different rows

New Contributor

Hi can someone please help?

I have two tabs of data, tab one contains 16000 rows of data and tab two contains 900 rows of data that contains the names of 900 different people from my organisation (first name and surname).  I quickly need to be able to see if any of the 900 peoples names appear on tab one (the 16000 rows of data), how can I do this please?

 

I am an Excel novice and have tried to search for the answer but to no avail.

 

thanks in advance

Scott  

3 Replies
Try =MATCH(A1, Sheet1!A1:A16000,0) if you got number values as output then this name is exist on sheet1. Otherwise you will get error mean doesn't exist.
Thank you for your response Harun24HR, apologies I am a total novice in Excel.

Where do i enter this formula? sheet 1 that contains the 16000 rows or sheet 2 which contains the 900 names of people?

@Pears19 

=COUNT(SEARCH(A1,sheet1!$A$1:$A$26))

An alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021. The range of the formula (sheet1!$A$1:$A$26) can be adapted as required.

sheet1.JPGsheet2.JPG

 

Result 1 means that first name x surname x is found on sheet1. Result 0 means that first name x surname x isn't found on sheet1.