Creating a Excel Sheet that auto populates based on data entered on a previous sheet

Hey all,

So I trying to figure something out for work. We use an excel workbook for our final reports. One of the sheets is called "snapshot" of the audit that we performed and it includes a location that we type in (but I am okay if we have to use a drop down to select). A second sheet that we use is our "Distribution List" which is who we are sending the report to.

Based on the location entered in the location section on the "Snapshot" sheet, we would like the "Distribution List" to auto populate based on the location (if it will auto format, that is a plus but I can work around that). See example below.

"Snapshot"
Location - ABC

"Distribution List"

John Doe - Director
John Doe 1 - Compliance Manager
John Doe 3 - Managing Director

vs.

"Snapshot"
Location - DEF

"Distribution List"
CC: Jane Doe - Director
Jane Doe 1 - Compliance Manager
Jane Doe 2 - Managing Director
Jane Doe 3 - Vice President