FILTER Formula to Return Non-Adjacent Columns in Any Order

Bottom Line: Learn how to return a limited number of columns that are not in the original order with the FILTER function in Excel.  Also discover how to make the formula dynamic to prevent errors when inserting and deleting columns.

Watch the Tutorial

Practice this technique on your own using the file that I use in the video. Download it here:

A Note About Dynamic Array Formulas

The Dynamic Array Formulas used in this post are only available for the latest version of Excel for Microsoft Office 365. Here is a post that explains more about Dynamic Array Formulas & Spill Ranges.

Returning Specific Columns in Any Order

If you've started using the awesome new FILTER function, then you might have run into the scenario where you only want to return a specific number of columns in an order that is different from the source range/table.

This was a great question from Melanie, a member of our Elevate Excel Training Program. She found a solution that allowed her to return individual columns, but they had to be in the same order as the source range.

One easy solution is to move the columns in the source range/table so that they are adjacent to each other. However, that is not always ideal or possible if you have multiple FILTER formulas for the same source range.

The good news is that we can use the INDEX function to create an array of non-contiguous or non-adjacent columns in an order that is different from the source data, which I explain in this post & video.

I also explain a technique for using a drop-down lists for the headers of the spill range so that you or your users can quickly change the columns that are returned.

Writing the Formula

Here is an example of the entire formula:

=FILTER(INDEX(tblData,SEQUENCE(ROWS(tblData)),{4,3,5}),tblData[Customer Name]=I3)

This can easily look complex or overwhelming when you first see it. However, we are just using an INDEX formula for the array argument of FILTER to create an array of non-adjacent columns. So let's look at that first.

The INDEX Formula Explained

The INDEX allows us to return an array or range of values to FILTER. INDEX has three arguments.

=INDEX(array,row_num,col_num)

Typically when you use INDEX you only specify one row number and one column number. However, we can also specify a list of numbers to return multiple rows and columns in a spill range.

There are two ways we can specify the list of row and column numbers in the INDEX arguments.

1. Using a formula like SEQUENCE or XMATCH to create the list.
2. In an array of hardcoded values in curly brackets: {1,2,3}

We are going to use both techniques in this formula.

1. Creating a list of Row Numbers with SEQUENCE

For the row_num argument of INDEX we need a list of all of the rows in the source range or table.

The SEQUENCE function will create this list for us. We only need to specify the rows argument in SEQUENCE and it will create a list from 1 to the number of rows specified.

We can use the ROWS function to return the count of the number of rows in the source range or table.

So, here is the formula for the row_num argument in INDEX:

=SEQUENCE(ROWS(tblData))

There are currently 80 rows in the tblData table, but this formula will automatically update as rows are added or deleted from the table.

2. Specifying the Columns to Return

For the col_num argument of INDEX we can specify the column numbers we want to return. For this array we can use the list of column numbers in curly brackets. We can also specify the order of the columns to return.

The following array will return columns 4, 3, and 5 from the table in that order.

{4,3,5}

So, the following formula will create an array of all the rows and only three columns from the tblData table.

=INDEX(tblData, SEQUENCE(ROWS(tblData)), {4,3,5})

The FILTER Formula Explained

The INDEX formula we created above is used for the array argument in FILTER.

=FILTER(array, include, [if_empty])

The include argument is used to specify the filter criteria, or rows to return from the array.

The great part about FILTER is that the column(s) you specify in the include argument do NOT need to be in the array. In the following example, the include argument is filtering the rows for a specific Customer Name. However, the Customer Name column is not included in the array that is returned by INDEX.

=FILTER(INDEX(tblData,SEQUENCE(ROWS(tblData)),{4,3,5}),tblData[Customer Name]=I3)

You can still use all the same techniques for specifying multiple criteria in the include argument. There are no limitations there. We do have dedicated training section on the FILTER function, including multiple criteria, in our Elevate Excel Training Program.

Avoiding Formula Errors with XMATCH

One issue that will eventually arise with this solution is when columns are added or deleted from the source data table. The formula will return values we don't want because we hardcoded those column numbers in the curly brackets. Those hardcoded values {4,3,5} will NOT change when the source table/range is modified.

To avoid this issue, we can replace the hardcoded list with an XMATCH formula.

The XMATCH function will do a lookup for the column headers that we specify above the spill range of FILTER, and return the column numbers of each column in an array (list). XMATCH returns a list because we are specifying a range of multiple cells in its lookup_value argument.

Here is the XMATCH formula:

Now when you add or delete columns from the table, XMATCH will automatically update to return the new column numbers based on the lookups that it performs.

A side benefit of this technique is that it also makes it easy to add columns to the results of the FITLER formula. You can just add a new column header in a blank cell to the right of the headers, then adjust the XMATCH formula to include that cell in the lookup_value range.

The spill range for FILTER will extend to the right with that one simple change.

Make it Interactive with Drop-down Lists

With the XMATCH function in place, one way to really make this list dynamic and interactive is to add drop-down lists to our headers. Then we can change the headers to any column name and the column will automatically update to return the respective column.

This means that you or the users of your file can quickly change the columns that are displayed in the resulting spill range of FITLER. There is essentially no maintenance or refresh required to make those changes. In other words, our arrays are very dynamic. 😉

Here's a tutorial on how to create those drop-down lists with the UNIQUE function: How to Create a Dynamic Drop-down List that Automatically Expands.

Conclusion

So that's how you change the column order of your return array. I admit that this solution is a bit complex. It may take some time to really study and understand each component, but it sure makes for a fantastic interactive end-result.

This is also a great example of how powerful and useful the new Dynamic Array Formulas truly are.

Thanks again and have a great week! 🙂

• Nikhil Venkateswaran says:

Wow, thanks, this is a great tip/example! I wasn’t aware of XMATCH to return an array, and the INDEX/SEQUENCE is really clever. This really helped to reduce the number of lookups in a number of spreadsheets.
Thanks for all the great posts!

• Kevin Carter says:

Good Morning Jon. I LOVE this formula, thank you again! I thought an addition may be helpful to some. Let’s say you always want this new table sorted by product. It’s a little tricky since you don’t know the columns or order the user will select, so you must use SortBy, and you need to sort both the full selection and the Include Selection.

I copied your headers to start bin cell L5 and put the following formula in L6. Seems to work well. I’ll bet we could even make the sort column dynamic using XLOOKUP inside SortBy, but I haven’t tackled that yet. 🙂

I hope this saves someone a little head scratching. All your help is very much appreciated.

Kevin

• Kevin Carter says:

Dynamic sorting using XLOOKUP inside SORTBY to get the appropriate column worked! I created a new page and used your dynamic column list far validation on the new pop up.

=FILTER(INDEX(tblData223,
“No Data”)

• […] Източник: FILTER Formula to Return Non-Adjacent Columns in Any Order […]

• Marc says:

Excellent !!!!

• Maurice says:

When I added data to the tblData table rows nor sequence rows with the table reference updated. i.e. it stayed at a count of 80 instead of 81

• Pri says:

Hi , this is great , thank you. Is there a way to append/add a new column the filtered table, such as add comments in a column and save?

• Kasia says:

Hi this is really great, but I have a question. I have used for one condition:
=IF(C10=’Data Validation Lists’!C1, FILTER(INDEX(TableSource,SEQUENCE(ROWS(TableSource)),XMATCH(A15:G15, TableSource[#Headers])), ((ISNUMBER(SEARCH(C12,TableSource[[Defined BLC Category ]]))*(‘Search Suppliers’!C11=TableSource[All Regions]))), ((FILTER(INDEX(TableSource,SEQUENCE(ROWS(TableSource)),XMATCH(A15:G15, TableSource[#Headers])), (ISNUMBER(SEARCH(C12,TableSource[[Defined BLC Category ]]))*(‘Search Suppliers’!C10=TableSource[All Approved Suppliers])*(‘Search Suppliers’!C11=TableSource[All Regions])), “No Match”)))))
but how do I incorporate if I have nested IF statement and the using AND:
=IF(AND(C10=’Data Validation Lists’!C1,C11=’Data Validation Lists’!D1,C12=’Data Validation Lists’!E1),(TableSource))
how do I Filter and combine together with the fiest IF statement?
Thank you. Kasia

• Jon says:

Apparently doesn’t work properly in Google Sheets. It’s only returning the first row. Not all.

• Nic says:

I love this. Really well done! Quick Question: Is there a way to print into a column, an auto-incrementing row number of the filter return? So if there are 10 rows pulled out of a 1000 row dataset by the filter function, can I print 1,2…10 in the column just to the left of the filter results **using the filter function**?

• Ekki says:

The formula worked like a dream! You saved my day! Thank you so much for sharing. You are wonderful!

• Chris says:

Jon – this is a fantastic formula. It is helping me understand more than one Excel function. I am a stickler for typos and I noticed FITLER was used at least a couple of times in your explanation above, rather than FILTER. Again, I think the solution you provided is great!

• Michel says:

how to use the formula

to get a multiple result for example Customer and order ID

• Sean says:

Hi Jon, thank you so much for this post. It solved one of my headaches. However, I have a question on creating row numbers with SEQUENCE:

I am referring to this formula:
FILTER(INDEX(tblData,SEQUENCE(ROWS(tblData)),{4,3,5}),tblData[Customer Name]=\$I\$3)

When I look up the syntax for INDEX(array, row_num, [col_num]), it says that you can omit the row_num argument to return the the whole column. I tried that (so that I don’t have to use the SEQUENCE to generate the row series), but it didn’t work. It returned #VALUE error. However, if you replaced the array constant of column {4,3,5} with only one value like 4, it worked.

Can you let me know why? Or refer me to some materials that can answer this question?

Thanks,

Sean

• Michal says:

Great tutorial how to squeeze Filter formula 🙂
I wonder if it is possible to filter and sort top 25 results with dynamic columns. I have list of customers (1000+ rows) with overdue at last 6 month ends. I would like to make 2 columns: 1) is the customer name, 2) overdue at month end. And here comes the tricky part – I would like to pick up from the dropdown list column 2, to be able to select i.e. January, so then excel shows top 25 overdue customers sorted by amount (column 2) with their names (column 1). If I change Jan to March, then again I get top 25 late payers sorted by the highest amount in March.

• Darin Scott says:

I’m not a fan of static ranges in formula’s “H5:K5”

By defining a range
ColRng = ‘Dynamic Columns’!\$H\$5:\$Z\$5
more cols than needed

use offset function, the formula doesnt need to be updated.

• Laura says:

Great article, this is really useful. How would I incorporate a top ten into this? I want to show the top ten (>=LARGE) rather than the full list – not sure how I add this into the formula. Any help would be much appreciated.

Thanks!

• Andy Souza says:

Hi,

I am using a filter formula for a project.
The formula is =FILTER(FILTER(Log!A:P,(Log!A:A”completed”)*(Log!C:C=”Yes”),”nothing found”),{0,0,0,1,1,1,1,0,0,1,0,0,0,0,0,1}).
I am trying to filter all cells that are not completed and have the word “yes”, which are several. The problem is, it is only using the filtering 3 rows, and I have more than 3 rows of “not Completed” and “yes” on the sheet I want to filter.
Does that make sense?

• ANDERSON J SOUZA says:

Hi,
I made a mistake on my last question. I fogot to include the sign.

I am using a filter formula for a project. The formula is =FILTER(FILTER(Log!A:P,(Log!A:A”completed”)*(Log!C:C=”Yes”),”nothing found”),{0,0,0,1,1,1,1,0,0,1,0,0,0,0,0,1}). I am trying to filter all cells that are not completed and have the word “yes”, which are several. The problem is, it is only using the filtering 3 rows, and I have more than 3 rows of “not Completed” and “yes” on the sheet I want to filter. Does that make sense?

• ANDERSON J SOUZA says:

Hi,
I made a mistake on my last question. I forgot to include the sign.
The correct formula is
=FILTER(FILTER(Log!A:P,(Log!A:A”completed”)*(Log!C:C=”Yes”),”nothing found”),{0,0,0,1,1,1,1,0,0,1,0,0,0,0,0,1}).

• Joaquim Maria Calainho de Azevedo Teixeira Duarte says:

Hi, thanks!!

Just thinking: for the ROWS argument can’t you use 0?

More because the table already reduced the number of rows..

I always got stuck at the {3, 5, 3},,, because the Portuguese version uses ;
but that return 3 values in that column!!

• Sylvie Biard says:

Hi Jon,
Thanks a lot, it’s been very usefull! But now using the new function CHOOSECOLS makes it much more easy.
Sylvie

• Paul Mark Sorauer says:

Why not simply use CHOOSECOLS with the column order you want?

• anon says:

Generic filters
Exact matches only

Excel Shortcuts List

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List