Large Data Drop Down Menu

On 50,000 rows, this might be slow!!

=IFERROR(INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$B$2:$B$11=Sheet2!$A$1,ROW(Sheet1!$B$2:$B$11)),ROWS(A$3:A3))),"")

... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...