Merging two worksheets and flagging matching names

I would like to take a large database of about 20,000 records, containing the names and many other types of information, and merge them with another worksheet, containing around 1000 names. (Those are just order of magnitude numbers).

In the real data, I want to identify names in the second worksheet that are also on the first worksheet. These represent "on premises" individuals. In my attached example, I use players from various Sports, and I want to match the names of the players on the second sheet that are also on the first sheet. These reporesent "the home team", which is analogous to matching the "on premises" names in my actual project.

I am not very astute at using things like vlookup, index, and match - or other functions, for that matter.

What I would like to do, when there is a match, is populate the "Home Team" field with the word Yes whenever there is a match. In my real world project, I will want to populate approximately 1000 columns with Yes when I find a matching on premises user.

Would someone be kind enough to provide me with the function or functions needed to match names in the two worksheets, and place Yes in the matchng fields, and provide a solution that will scale to thousands of entries? I'd sure appreciate it. Once I understand how to do this properly, I should be able to apply it to a variety of real world scenarios that I expect to be working on in the near future.

I sincerely thank you for your help - and would also appreciate an explanation of the solution so that I can then go back to the documentation and apply the techniques.

Beautiful! First question, first answer, both worked like a champ! When I ran it on my test data, it worked great.

Copying the formula into the database, which is over 18,000 records long at the present time, did not work as smoothly as I would have liked because some of the values of my "OnPrem Acct" (the 'home team' in my example), were already populated, so a quick "Go to last entry" did not work out, and I had to sit there for a couple of minutes scrolling to the bottom.

I also appreciated your explanation.

In an exercise that is coming up soon, I will have to merge account records of a master database. The "Master Database" is essentially a site customized LDAP type of file with directory information about ndividuals with Email accounts. The master spreadsheet I am dealing with contains additional information about some activities involving both calendar and Email migrations - either that have already taken place or are being scheduled to take place.

When the project is complete, we should see every single row either marked as complete or as to be removed from the system.

I am expecting, if I do this right, that I ought to be able to do all kinds of reports off of the master spreadsheet - what's done, what's not done, what percentage of accounts have been migrated, what is the rate of migration, what percentage need to be completed, who is scheduled this week, and so forth.

I am sure I will have more questions, but hopefully your great example and explanation will result in fewer questions on my end.

Thanks again, it worked great.