Filter formula in Excel 2016 Pro Plus

Gilberto Sanches
Gilberto Sanches used Ask the Experts™
on
Hi Experts, in Google sheets the filter function/formula is easy to use. Am using it for months now.

I was expecting the same in Excel. However, the Excel that we are using doesn't has the filter function. The function we found with filter in it's name is FilterXML.

Excel is being used for a remote production area. So no access to internet.
What formula alternatives are there? Or how can I find a formula like filter in Excel?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
For some people, there is a FILTER function in Excel 2016 on an Office 365 subscription. It is associated with the dynamic arrays initiative and was introduced to Office Insiders about a year ago. That said, it has not reached General Availability as Microsoft's beta testing of dynamic arrays is still underway.

When the feature is released, it will be available to everybody with an Office 365 subscription. It will not be available for people using the perpetual license versions of Excel 2016 or 2019.

If you want to make a list of items meeting certain criteria, you can do it using INDEX and AGGREGATE functions. For example, suppose you want to list values from column C where column B equals "ABC Company" and column D is over 5000. The formula will be placed in cell H2. You might copy down this formula to return all the items in your filtered list:
=IFERROR(INDEX(C$2:C$1000,AGGREGATE(15,6,(ROW(D$2:D$1000)-ROW(D$2)+1)/((B$2:B$1000="ABC Company")*(D$2:D$1000>5000)),ROWS(H$2:H2))),"")

Open in new window

Although the preceding formula contains an array expression for the two criteria, it does not need to be array entered. Because of the array expression, the formula will calculate much faster if you aren't testing entire columns.
Gilberto SanchesFreelance ICT Consultant

Author

Commented:
Hey Byundt, thanks for clarifying & offering a solution. I tried the formula in my situation but it gives me a error "you've entered too many arguments for this function". So I am searching for what I did wrong or maybe I didn't understood it well.

In the Index function you put the column from which you want the list.
Aggregate does the filter (column to filter, the criteria for filtering)
What is the row function for?
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
The ROW function returns the row number(s) of the cells in its parameter. If there are more than one cell, the ROW function returns more than one row number.

The ROWS function returns the number of rows in the range represented by its parameter.

The formula I posted works on my computer. There are a lot of moving parts in it, so please post what you tried--I'll try to debug it for you.
Gilberto SanchesFreelance ICT Consultant

Author

Commented:
Alright. Good to know Byundt.

My situation is the following.
There are 2 sheets.

In sheet 1 have the result of the formula. Sheet 2 has the data.

The filtering result I want from sheet 2 column B.
The criteria column is in sheet 2 column E.
The criteria is sheet 1 cell A2
The results I want in sheet 1 Column A starting at row 3

The formula would be
=iferror(index(sheet2!$B$2:$B$50000,aggregate(15,6,row(sheet2!$E2:$E$50000=$A$2))),"")

What is not good in the formula?
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
AGGREGATE needs four parameters. Your formula has three. The missing fourth parameter is the number of the desired value (1 = first, 2 = second, 3 = third, etc.). I assume your formula goes in cell H3 and will be copied down to return the additional filtered values. I used ROWS(H$3:H3) for that purpose.

The third parameter of AGGREGATE returns index numbers of every row satisfying the criteria. The numerator returns index numbers, while the denominator tests whether the criteria are satisfied.

The criteria is the denominator, and returns an array of TRUE or FALSE. When used in an arithmetic expression as they are here, TRUE becomes 1 and FALSE becomes 0. If a row doesn't need the criteria, the denominator is 0 and an error value is returned. The 6 in the second parameter means to ignore error values.

The numerator needs to be the numbers 1 through 49999 rather than the actual row numbers. The INDEX function needs index numbers 1 through n to specify whether you want the first, second or nth value in its first parameter. That's why I subtracted ROW(A$2) and added 1.

Your formula used the ROW function and tried to include the criteria. It will fail because the smallest umpteen values it returns will be 0--representing rows that failed the criteria test.

Taking it all into account, I think you need a formula like:
=IFERROR(INDEX(Sheet2!$B$2:$B$50000,AGGREGATE(15,6,(ROW(Sheet2!$E$2:$E$50000)-ROW(Sheet2!$E$2)+1)/(Sheet2!$E2:$E$50000=$A$2),ROWS(H$3:H3))),"")

Open in new window

Gilberto SanchesFreelance ICT Consultant

Author

Commented:
Your explanation helps. I also tested your formula, it had one closing bracket too much. The formula was accepted but it doesn't show the results yet.
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
Copied straight from my workbook:
=IFERROR(INDEX(Sheet2!$B$2:$B$50000,AGGREGATE(15,6,(ROW(Sheet2!$E$2:$E$50000)-ROW(Sheet2!$E$2)+1)/(Sheet2!$E2:$E$50000=$A$2),ROWS(H$3:H3))),"")

Open in new window

If it isn't working in your workbook, could you please post your workbook?
Gilberto SanchesFreelance ICT Consultant

Author

Commented:
Heyyy Byundt, good mornin. Just started at it again and it worked. I had it wrong at the row & rows part. I get it, thank you very much man

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial