Excel Name Matching Automation

I maintain several Excel sheets and need a reliable way to spot specific names by comparing one sheet against the master list stored in another. I’m looking for a streamlined solution—preferably a VBA macro, Power Query setup, or a well-structured formula stack—that will:

• Cross-reference the master sheet and flag or extract every matching name from the other sheets.
• Return a clean, de-duplicated list of matches in a separate tab.
• Refresh with a single click or on file open, so I can reuse it as new data comes in.
• Be easy for me to tweak later if the sheet layout changes slightly.

A brief walkthrough or inline comments are important so I can understand and extend the logic. If you have suggestions on best practices for scalability or performance within Excel, feel free to include them—just keep the final file lean and user-friendly.