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.

 

[WarfritLive]

I'm sorry, I've read this several times over and I don't understand. Does your picture have anything to do with your example?

 

[highway6]

Yes. Say columns B and C are unique data. Column A is a hidden column performing an array of if tests.... resulting in a status of 0,1,2,or 3.
Elsewhere, say another tab.. I want to run =filter() and only display the column B data when A=1.
In google sheets I could do =filter(a:a,"1",b:b) where arguments are (search array, search term or cell, result array).

The returned result in this case would be 1,2,5 displayed in the cell with the formula and the two(in this case) cells below.

Incredibly simple and useful.
And in excel it vlookip doesn't do the trick, at least not alone.

 

[WarfritLive]

How about =IF(A1=1,B1, "" )

 

[highway6]

So for 100s of rows of data.. you want I should do an if statement per row ?
Seems inefficient.

 

[highway6]

Also.. that would put empty cells on rows that don't match.. so that there could be tens or more of empty cells between matching results.
Let me give an example of how I've used this...
To reconcile my check book.. every credit card entry gets 1 of 6 dif use codes.. g for gas/car.. d for dining out.. b for bills remember etc. .... on a dif page I could have an summary that reads though 100s and 100s of rows of data and only displays entries where month equals so-in-so and code equals gas.... then it would display those 10 entries in 1 com in consecutive cells.
I can't do that with if statements without convoluted mid steps.

 

[WarfritLive]

Unfortunately Excel doesn't have a comparable function to display things like that. There are numerous ways to generate an array with the cells you need as well as their locations, and of course the actual filter button. But there's no function to do this the way you're trying to do it

 

[FDibbins]

And unlike Vlookup, It returns more than just the first instance of the search value.

Sounds like you need to use 1 of the more advanced ARRAY formulas...
index/small/if
Not able to give more detail until you provide samples of what your data looks like

 

[FDibbins]

But there's no function to do this the way you're trying to do it

Actually, there is

 

[FDibbins]

As an example...

	A​	B​	C​	D​	E​	F​	G​
15​	Company	Code	Description		Bike Parts
16​	Bike Parts	100	Wheels		Bike Parts	100​	Wheels
17​	Bike Parts	120	Handlebars		Bike Parts	120​	Handlebars
18​	Bike Parts	110	Body		Bike Parts	110​	Body
19​	We Like Bikes	110	Body
20​	We Like Bikes	120	Handlebars
21​	Bike Store	130	Axles
22​	Bike Store	120	Handlebars
23​	Bike Store	100	Wheels
24​	Bike Store	110	Body

E16=IFERROR(INDEX(A:A,SMALL(IF($A$16:$A$24=$E$15,ROW($A$16:$A$24)),ROWS($A$1:A1))),"")
ARRAY entered, using CTRL SHIFT ENTER, not just enter
Then copy down and across as needed