Pulling in all SSNs with a certain city and state in an Excel table

I think the solution here is an array formula, but I'm not familiar with those and thought I'd ask before trying to learn.

I have a large table with personal information including city and state. I am creating, on a separate tab, a drop down where you can select city and state, and it pulls in the names and SSNs of all the people who match that city and state. I was able to create the drop down, but I don't know what formula to put in the SSN column of this table so that all SSNs appear based on the cities and states selected in the drop down.

For instance, if I chose New York in the City and State drop downs, I want to produce a list that shows 00000001...Jane Doe, 000000002...John Smith neatly in the table without having to do any work manually. Extra points if I can make it so there is always a border on the last item in the table (e.g., if I choose Reno Nevada next and there are only 3 people who fit the criteria, I'd like for the border to shift up too)

Let's say the data is on a tab name 'Data' with SSNs in column A, Names in column B, States in column C and Cities in column D.

** Disclaimer, it's not actually cities, states and SSNs that I'm using, but I thought it was a simple example. **

Hello and welcome the forum.

I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

.

** Disclaimer, it's not actually cities, states and SSNs that I'm using, but I thought it was a simple example. **

Certainly hope that is true. Cuz I'd wonder what you are doing with all those SS numbers.

Okay, I believe I added an attachment with a simple sample, including my notes on what I would like to happen

No worries, it's a different unique identifier.

bump

Try this in A7:

=IFERROR(INDEX(Data!A:A,SMALL(IF((Table1[City]=$B$2)*(Table1[State]=$B$3),ROW(Table1[SSN])),ROWS($1:1))),"") Ctrl Shift Enter

Drag the formula through D7 then down as far as needed.