excel equivalent to google sheets =filter()

highway6

New Member
Joined
Dec 21, 2016
Messages
8
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.

344qsco.jpg
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
As an example...
A​
B​
C​
D​
E​
F​
G​
15​
CompanyCodeDescriptionBike Parts
16​
Bike Parts100WheelsBike Parts
100​
Wheels
17​
Bike Parts120HandlebarsBike Parts
120​
Handlebars
18​
Bike Parts110BodyBike Parts
110​
Body
19​
We Like Bikes110Body
20​
We Like Bikes120Handlebars
21​
Bike Store130Axles
22​
Bike Store120Handlebars
23​
Bike Store100Wheels
24​
Bike Store110Body
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
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top