Excel FILTER Function to Return Non-Adjacent Columns in Any Order

No video

Excel FILTER Function to Return Non-Adjacent Columns in Any Order

Рет қаралды 40,392

Күн бұрын

Sign up for our Excel webinar, times added weekly: www.excelcampu...
In this video, you’ll learn how to filter non-adjacent columns in any order. You can return any number of columns that are not in the original order with the FILTER function in Excel. I also explain how to make the formula dynamic to prevent errors when inserting and deleting columns.
If you’d like to read the accompanying blog post on my website, you can access it here: www.excelcampu...
Related Videos:
Dynamic Array Functions & Formulas that Spill: • Dynamic Array Function...
Dynamic Drop-down Lists: • Dynamic Drop-down List...
UNIQUE & FILTER Functions: • UNIQUE & FILTER Functi...
#MsExcel #ExcelCampus
00:00 Introduction
01:19 Writing the formula
06:12 Inserting and deleting columns

Пікірлер: 111

@noelgreen6198 Жыл бұрын

I’ve been using Excel for well over 30 years and this has got to be one of the most useful formulas I’ve ever seen. Thanks a lot for the clear explanation. Great post.

@iankr Жыл бұрын

Wow! These (relatively) new array/spill functions have delivered a mind-blowing level of powerful flexibility into the handling, analysing and reporting of large datasets. I've been learning a lot about these dynamic functions over the past few months (from your videos as well as from other MVPs) for a work project, and they have proved to be so useful. I am now teaching them to my colleagues. The use of XMATCH() to pick the columns returned adds yet another level of flexibility. Very useful and powerful. Many thanks for your clear demonstration, Jon.

@sailor7025 3 жыл бұрын

REALLY neat formula Jon - almost Access like behavior. As always, you are the best Excel teacher on the planet!

@UTJK. 3 жыл бұрын

You're a master. This is a complete equivalent to a SELECT... WHERE in SQL. Thanks for sharing!

@OneTrueBadShoe 2 жыл бұрын

I wish I'd found this sooner. I have been trying to figure out how to do that for months! Thank you. This is exactly what I needed.

@esthersuh3388 2 жыл бұрын

Your explanation for index function to lookup multiple non-adjacent values was very insightful and easy to understand. Thanks,

@lmajiedmary 2 жыл бұрын

Wow! Exactly what I was looking for and it was explained so clearly. Thanks very much!!

@josephharmon1202 2 жыл бұрын

This is magic. I was using a query function to pull info for 10 different variables into a "report". Really slowed down excel when trying to refresh... will definitely be using this.

@funsmart2001 2 жыл бұрын

Thanks Jon, i was searching for complete day to get this formula you made it very easy

@pjrosenburg9243 Жыл бұрын

Awesome man. Just awesome.

@leodupont2418 2 жыл бұрын

This is so amazing! I've been looking for this solution for months.

@GeorgeAJululian Жыл бұрын

Thanks very much very helpful

@williamarthur4801 2 жыл бұрын

Also it inspired me to mess around with other options such as using {} for both rows and columns and trying to force a sort of matrix result by using comma and semi colon and then reversing so semi's for columns and commas for rows. You get some interesting results.

@usmaniqbal1836 2 жыл бұрын

Perfect Sr!

@TalhaShakeelpak Жыл бұрын

Thanks to both Melanie and Jon

@Stvnsmcrn Жыл бұрын

it actually works, thank you so much ❤❤❤

@ExcelCampus Жыл бұрын

You're welcome, Steven!

@mehrzadpakzadeh 11 ай бұрын

Thanks Jon. Your post was the solution for me to create a dynamic chart by using filter function. I tried all other ways such as PivotChart and struggled to find the best and quickest solution. Thank you again for video. Keep it up :)

@ExcelCampus 11 ай бұрын

Glad it helped, @mehrzadpakzadeh! 😀

@JoeyJoaz 2 жыл бұрын

Best solution so far now I can skip filter filter or filter choose woot woot

@10ozGold 2 жыл бұрын

Excellent video, Jon! Keep up the good work.

@jrodclark Жыл бұрын

This is just totally awesome. No other way to describe it. Thx.

@ExcelCampus Жыл бұрын

Glad you like it, Rod! 😀

@KrishnaKumar-zn9kg 2 жыл бұрын

Absolutely fantastic! What I always wanted to have, but never did! Thanks a Ton, Jon! You are a saviour! 👏

@ExcelOffTheGrid 3 жыл бұрын

A similar result can be achieved with CHOOSE, but I think the INDEX solution is much neater. I like the addition of XMATCH. Good work.

@axion8788 2 жыл бұрын

Excellent methods, excellent job of TEACHING (which is much more than showing). Subbed.

@williamarthur4801 2 жыл бұрын

Also you can perform the same trick for rows by using INDEX(Range,{rows to return},TRANSPOSE(SEQUENCE(COLUMNS(range)))) Just remember use semi colons between row number unless using the match dynamic option.

@paulbermejo5735 6 ай бұрын

You just saved me! I was struggling for almost a week trying to find for a solution to my issue. THANK YOU!!! You basically saved from getting fired ^_^

@ExcelCampus 6 ай бұрын

Glad I could help! 😀

@m.raedallulu4166 2 жыл бұрын

That was "Excell-igent"! Thank you, sir!

@pkuchnicki 2 жыл бұрын

Awesome!!

@maheshjagwani9382 2 жыл бұрын

have been looking for this solution, thanks lot

@anuragkumar7317 2 жыл бұрын

Thank you very much. This is what I am looking for.

@ezolau 3 жыл бұрын

it is a great solution

@Everyonelovesyou 3 жыл бұрын

Best explained. Sir Jon! Thanks

@vothuong1499 2 жыл бұрын

Wow, amazing, this is very helpful for me! Thank you very much!

@rperea73 Жыл бұрын

Thank you. Greetings from Panamá. 💯👍🏾

@toufiktoufik1425 2 жыл бұрын

Very useful trick thanks awesome!!!

@wayneedmondson1065 3 жыл бұрын

Hi Jon. Great video! Really like your second solution with XMATCH and the drop down column headers.. clever! Here's one that I use to avoid column insert issues. It uses CHOOSE instead of INDEX and an array constant on the table column numbers. So, you use CHOOSE to assemble the output columns you want and their order which becomes the array for FILTER. =FILTER(CHOOSE({1,2,3},tblData[Product],tblData[Category],tblData[Unit Price]),tblData[Customer Name]=I3). Always great learning at your channel and web site :)) Thanks for sharing and Thumbs up!!

@williamarthur4801 2 жыл бұрын

Another great tutorial, I've been using similar idea to bring back certain rows {} but using the ; semi colon to separate.

@diannedrechsler4792 3 жыл бұрын

Great solution, thanks Jon!

@777kiya 2 жыл бұрын

Beautiful, you can also use filter or filter using required column indexes in curly braces, in the outer filter function.

@dejabluek8297 3 жыл бұрын

Thank you Jon! This is fantastic!

@idkt2587 3 жыл бұрын

Amazing tutorial! Thank you!

@darrylmorgan 3 жыл бұрын

Great Tutorial,Real Cool Formulas...Thank You Jon :)

@ExcelCampus 3 жыл бұрын

Thanks Darryl! 🙌

@christiandeinzer4136 2 жыл бұрын

Fantastic!!! Great job!

@flootsy 3 жыл бұрын

Great video as always.

@CaptainGlanton 2 жыл бұрын

This is so useful!

@mathewsabraham8962 2 жыл бұрын

Great solution.. thanks a lot

@aguerojg 3 жыл бұрын

Thank you very much for this great tutorial, Jon, very interesting and shows the power of the new Filter function :)

@ExcelCampus 3 жыл бұрын

Thanks Juan! I agree about the power of FILTER and the dynamic arrays. I have said it a few times now, but I think there is still A LOT to be discovered and created with these new functions.

@EricHartwigExcelConsulting 3 жыл бұрын

Seriously Cool! Thank you Jon!

@nadermounir8228 3 жыл бұрын

Great tutorial 👌. Thank you for these tricks 👏

@ExcelCampus 3 жыл бұрын

Thanks Nader! 🙂

@lastdance903 3 жыл бұрын

Jon, this is really powerful. Thank you!. I’m still using pivot table as my search toolkit and the new filter function is apparently way much better. However, I noticed that the filter on the customer at the very top is still a drop down list, which can be less handy when you have a long customer list. I’m wondering if you can do a video about dynamic search box? Great work. Love it!

@AMANDEEPSINGH-uk3zb 3 жыл бұрын

amazing video from amazing excel superstar

@pralhadshetye8863 3 жыл бұрын

Amazing

@SimonTidd 3 жыл бұрын

Awesome as always Jon. Now I just need a use case to apply it to

@ExcelCampus 3 жыл бұрын

Thanks Simon! 🙂 Great point about use cases. The FILTER function can be used instead of a pivot table (in some scenarios) to create reports, and often times you will only want to include a few columns from the source range in those reports. I'll think about some examples and share them in the future. Thanks again and have a nice day! 🙂

@sachinrv1 3 жыл бұрын

Wonderful video. Thanks for sharing :)

@joncannaday250 2 жыл бұрын

ArrayToText(Filter(Single column of return values , value = range , ),) look up value within duplicates and return all values for that lookup separated by ;

@felipelandim2881 3 жыл бұрын

Cool. Really like the channel.

@imranisshack9828 3 жыл бұрын

@ Excel Campus. Hi Jon. This is one of the most useful Excel Tutorial I have watched. Thanks very much for this. Question: how do I add an additional criteria to your formula, let say for "Product Field" or add multiple Criteria? =FILTER(INDEX(tblData,SEQUENCE(ROWS(tblData)),{4,3,5}),tblData[Customer Name]=I3)

@lettysantos7753 3 жыл бұрын

Is this part of VBA. I like the way you explained not so fast and I Iike your voice.

@rickyleekakit8773 3 жыл бұрын

great

@grahamparker7729 3 жыл бұрын

That’s great, thanks Jon

@ExcelCampus 3 жыл бұрын

Thanks Graham! 🙂

@jamesvanderham6210 Жыл бұрын

There is a new formula called CHOOSECOLS that just gets an array and then you choose which columns and what order you want them. You can even have a column multiple times if you want, like col 2, 3, 5, 5 again. Might need to do some MATCH formula stuff to get the column numbers to match dropdowns etc. Jon should make a video on that formula.

@syedaneesdurez7197 Жыл бұрын

Amazing filter formula.. Appreciate for sharing .. Can we use this formula in array ..Mean to say filtering data by multiple customers at a time rather than specific one.

@petecardona8203 Жыл бұрын

Hi Jon, an excellent explanation as usual!... have a question, how do I create a combined filter & large formula for, let's say returning two criteria like the "top 10" selling items for a particular "subcategory". I just want it to return, the top 5,10, etc selling items for that subcategory...I am aware I can do it in a Pivot table easily, but I'd like to think outside the box and learn it this way. Thank you again!

@profocelsomarin4123 2 жыл бұрын

ganhou um inscrito!

@deesquare08 2 жыл бұрын

Thanks a lot Jon. Very nice and easy to understand solution. Can you explain how I can count the total nuber of customers and the total number of purchase greater than $10?

@prathapj7185 Жыл бұрын

Awesome 😎, I kind of looking for a similar solution for months but in my case the filter criteria is moth. I need to retrieve the data for hole month from a different dates. Could you please help me on this.

@raf4imf 3 жыл бұрын

Fantastic …it solved a stumper for me! Question: can a cell reference or formula for each number in the Index formula array be used rather than the number itself? If so, then xmatch can be used to find the column number from the source table and automatically change if the headings are changed in the target table, e.g., change the order of or even different target table headings. By the way, I put the Sort function at the start of your final formula to sort the filtered table on the first column. That way I can easily sort on different headings by putting the one of interest in the first column. Thanks so much for this excellent video!

@jamesgilbert8070 3 жыл бұрын

Brilliant

@ExcelCampus 3 жыл бұрын

Thanks James! 🙂

@rahulchheda1058 3 жыл бұрын

Thank you Jon for this video. Can we add multiple items in include like Select customer and OrderId

@xianleepersaud 2 жыл бұрын

This is great! I have one question, if I wanted to adjust the value of unit price according to the search in a next column outside of the search area, how can I do so? The next column remains static when I try.

@christiandeinzer4136 2 жыл бұрын

I have been trying to add a COUNTIF function to count how many repeated lines from the range A6:F100 are inside the range H6:J12. Thanks.

@336kp 3 жыл бұрын

Thanks Jon this is great. Can I nest with the sort or sortby function to this filter formula?

@raf4imf 3 жыл бұрын

Yes... just add Sort to the beginning of the formula to sort by first column.

@nicow6808 Жыл бұрын

What if you want to filter more than 1 item (food 4less and saveway) . Can you use this formula with a table filter?

@lauravanweegen5954 3 жыл бұрын

Very useful tutorial thank you, especially love the matching option. Do have one question, you mention that can you build multiple filters into the formula. So how would I extend your "Customer Name" formula below to achieve this? My Excel skills are limited so I'd probably just add a "+" & recreate the formula with new filter and hope that it works. 😁 =FILTER(INDEX(tblData,SEQUENCE(ROWS(tblData)),{4,3,5}),tblData[Customer Name]=I3)

@ratneshbansal5139 3 жыл бұрын

Since we are automating this function. Could we not put a mix of offset & counta function within xlookup to search the last or blank cell for the range to give the data under sequence function?

@devinstephenson1362 2 жыл бұрын

Hi Jon, is there a way to apply the Unique function into this formula to only return unique values from the "Product" column as opposed to returning unique values across all three columns?

@crisjohnwilllim9444 3 жыл бұрын

Hi Sir, Another Amazing video. Unfortunately I dont have filter function in my excel, is there an alternative way to do this?

@josephchristianvillarama8241 Жыл бұрын

Hi, just want to ask what if the customer name are "Whole Foods" and "Food 4 less" and I want to get both results by selecting "Food". How would the formula look like?

@ricos1497 3 жыл бұрын

Nice function. Isn't it frustrating that you need the FILTER at all? When you're using the INDEX() function, it would have been much better if the Excel team had just allowed INDEX to ignore zero values. Thus: =INDEX(tblData,SEQUENCE(ROWS(tblData))*(tblData[Customer Name]=I3),{4,3,5}) Would work exactly the same way as filter but save the need for a whole new function. I think anyone using FILTER would benefit from understanding INDEX first and FILTER uses "complex" and/or criteria for its filtering anyway, so I can't see the benefit of the separate function. Oh well! Great video nevertheless!

@ExcelCampus 3 жыл бұрын

Thanks Rico. Great point! The simplicity of the FILTER function is definitely going to make it accessible to more users, but it would be nice to have an easier way to return individual columns from an array.

@redhaakhund1271 3 жыл бұрын

⭐️⭐️⭐️⭐️⭐️

@nadermounir8228 3 жыл бұрын

I have one suggestion or a question : when we select unit price using XMatch, the cell format doesn't change to currency format. For the cell format to automatically change will need to use VBA change event. Is possible to cover this one?

@ExcelCampus 3 жыл бұрын

Hi Nader, Great question! Right now, the spill range does not display the number formatting from the source range. Hopefully this will change in the future. There are a few workarounds. One solution would be to use conditional formatting. You could create a few conditional formatting rules that changed number formatting of the column depending on the value in the header row above the spill range. This would be the most dynamic solution and easiest to implement. You could also use VBA for this. With VBA you could use a worksheet_change event to run the macro when a change is made to the header cells above the spill range. The drawback to VBA in this case is that the user could not undo their action. You would lose the undo history when the macro runs. I'll add this to our list of videos for the future. We also cover some conditional formatting with spill ranges in our Elevate Excel Training Program. Thanks again for the question! 🙂

@MrTEShero 3 жыл бұрын

Hi Jon, lets say i want to put my result into a data validation list, how do i accomplish this? As it is currently the data validation list does not accept index or filter functions. I have multiple data validation drop down lists that will be creating their own version of the data so i value it rather troublesome to have an entire sheet as helper columns for this functionality, do you have any advice? //Best, Teshero

@williamarthur4801 2 жыл бұрын

I know I'm becoming a bit of bore on this but this will be my last comment on this topic. I'ts this; how about reversing the process slightly and start with the filter so; INDEX(FILTER(Range,Filter range='Criteria"),{Rows to return},SEQUENCE(,COLUMNS(columns in range))) This way you can filter for the required rows and then rearrange those rows in any order. Still experimenting with columns.

@pietjehans206 3 жыл бұрын

Thanks for the solution! Unfortunatly it doenst work for me. By using {1;3;4} it doens't spill columns, it tries to put it all in the same row. Am I missing something? =INDEX(Tabel1;REEKS(RIJEN(Tabel1));{1;3;4}) Or translated: =INDEX(Table1,SEQUENCE(ROWS(Table1)),{1,3,4}) Output: Column 1 Column 3 Column 4 #N/B #N/B #N/B etc.

@pietjehans206 3 жыл бұрын

Finally found my mistake the European matrix seperator is \. So it had to be {1\3\4}. So unnecessarily confusing.

@MeNinzaaa 2 жыл бұрын

@@pietjehans206 Thank GOD I found your comment, I have been fighting with this for a whole day!

@pietjehans206 2 жыл бұрын

@@MeNinzaaa try using the \ separator for European Excel version. For example {1\3\4}. Took me another day to find out 😀

@MeNinzaaa 2 жыл бұрын

@@pietjehans206 Worked like a charm! It's bad enough I have to change all comma's to semi-colons in formulas I find online, without all these other totally ridiculous regional differences :')

@ParWallgren 2 жыл бұрын

@@pietjehans206 Who in MS thougt it was a good idea translating formulas and functions to different spoken languages??? Anyway THANKS! Struggled all day trying to find the error. It was the matrix separators...❤

@eljinet 2 жыл бұрын

So on Google Sheets I have this: =iferror(FILTER('RAW DATA 2'!$B$2:$G,'RAW DATA 2'!$B$2:$B>=$B$1,'RAW DATA 2'!$B$2:$B

@roneit 3 жыл бұрын

Hello! I'm trying to use the INDEX function skipping columns in Google Sheets, but I guess the exception {1,2,5} doesn't work there.

@a1e2l3p4 2 жыл бұрын

Does anyone know why I don't get the table with all the rows specified in curly brackets? Tried several time but everytime I get only one value and not the whole filtered table((((