excel equivalent to google sheets =filter()

[highway6]

the subject says it all.
Google sheets has a very useful function, filter(), that allows you to search one column for data, and return a value in another column. And unlike Vlookup, It returns more than just the first instance of the search value.

What formula could I use to search A:A, and for every instance it find in a search cell (Say E1 is 2 in this case), and it returns the equivalent value in B:B. So for search cell 2, it would return "3" and "6" in the cell below, and so on and so on.

 

[highway6]

I think we are getting somewhere.. and I appreciate that you've gotten what I'm trying to do.

your code isnt working for me yet.. I'm getting a circular reference error.
Also the last ($A$1:A1).. is that an error.. seeing as such nothing else in the example uses A1? I also used A16:A24 there to no avail

 

[highway6]

Also.. realized one difference.. in your example, you are using Column A for search, but also displaying A.
In my example, A is for search only, a hidden column... I want to lookup in A, to display B, then lookup in A to display C (next formula over), etc.

 

[highway6]

Actually.. I guess my last post, displaying the column with the search term isnt the end of the world as a) it can be hidden and b) thats what the google filter command does.

To demonstrate more clearly what I'm trying to do.. Here's an example I did in google sheets.
G5 is the only cell that contains a formula... it searches through B:D, for G3 and displays all matches.

Seriously.. why Excel doesnt have this is beyond me.. and excel users that only use filtering in the tab.. you have no idea the power you are missing out on.

 

[highway6]

bump.