Mike, you read my mind! I built the first formula with the multiple matches inside FILTER late last week. Shortly after that, I decided to go find someone who figured out how to Filter match a list this weekend. Sometimes I need to match 100 or more items from a table and need the table format. Sunday, I get a notification and see that you posted exactly what I needed. I'll be using this one starting tomorrow. Thanks again for all the great videos!
Hi Mike, I looove your videos! 🤩🤩🤩 I've learned so much over the years, all my Excel is self-taught by watching so many of your videos and doing web searches for solutions and ideas, but you are a hero, and the best at that! Your style is unique, you always sound so friendly, and you have the best step by step tutorials. Thanks for the good work!
Thank you so much for this! I'm trying to teach myself data analytics, so I'm also teaching myself Excel. I've been beating my head against the wall for the past hour trying to figure out how to do exactly this--thank you so much for such a quick and accessible video!
Mike!! ... AGAIN... MASSIVE .... THANK YOUUUU!!! 🙏🙏🙏🙏🙏🙏🙏 !!!! somebody asked me if it was possible to filter the graphs by two or three items or more (or based on a list) by the multiple selection from pivot table slicers...and this tutorial has the elegant solution! It's GOLD 🏆🏆🏆🏆 #GoTeam !!!!
Wow. I hadn’t paid attention to XMATCH before this tutorial. Some of my Excel applications suffer from sub-optimal solutions that XMATCH will solve. I look forward to applying this new knowledge. Thanks, Mike!
Thanks Mike-- I just needed this and it brought me to your trusted channel 👍👍 - Im really not using XMatch enough, I feel its the "Lesser used" of the newer formulae!
The bad news is I needed this last week....the good news is I will need this again next week. That was a great explanation. Thanks for sharing Mike....
The bad news is that I have done this trick about 20 times during my 12 years at RUclips (not all with FILTER function but always with MATCH). The good news is that I finally marketed it well , and FILTER is now in all of Microsoft 365 ; )
@@excelisfun But the better news is that to find the items in the short list that are NOT in the dataset, simply switch out IsNumber for IsError to return the Falses. Works a charm. Thanks Mike!
Thank you this works great! I do have a question when I copy and past the list into the “expense category “ In your case and it doesn’t filter results. However when I enter it manually it does filter the list. What am I missing? Is it a setting issues? Thanks, Michelle
It would be so great to see a tutorial completely dedicated to comparing 2 columns for duplicates, matches, different sizes etc. and all the to do's and don'ts. A common task we all struggle with. Thanks Mike!!!
I have over 30 videos about comparing two lists and a whole playlist dedicated to just this topic. If you ever have a topic, just search my home page. Here is compare two list playlist: ruclips.net/p/PL83E0D7B674B73A06
For me, this is one of the best examples of why M 365 Excel is so amazing. We used to build reports like this with very complex array formulas back in the day, but now? It is very easy : )
@@excelisfun yes I remember these old long array formulas and I learned them also from you. Index with Small function with If and row and rows for the K of the small function. U also were using aggregate to avoid control shift enter. I learned a lot from u Mike
Yes, comparing two lists with XMATCH, or MATCH in the old days, is one of the more common tasks throughout Excel History. It is just made a lot easier with the new and amazing FILTER function.
Awesome, just awesome. I've been trying to do this for the past couple of months. Thanks for helping out. I am wondering if we can do something similar by matching just one data name to multiple instances and then populating a table with all those results?
Thank you Mike sir. Your excel videos help us to solve our daily problems which we face in our day to day work. Keep sharing as your video give us insight about excel. Thank you. 😊😊😊
Another great one. Great little trick: if for some reason you want to exclude the expense category numbers just use ISNA instead of ISNUMBER I’ve been using xmatch to check if multiple values exist or not in a range a lot more than the isnumber (which requires MMULT)
Yes, Advanced Filter is Old School, but less efficient because it does not update. When everyone switches to Microsoft 365, like VLOOKUP, there will be very few cases were we would still use Advanced Filter. But maybe still a few : )
Thanks for another great learning experience. Is it possible to do the same argument operation on two columns and then filter it to matching column headers? (Repeats in both columns)
I've been searching everywhere for more info on array logic with MATCH (and XMATCH) to expand my skills, like what you just demonstrated using an array in the lookup_value, or using 1. I've seen these at work, but there's never an explanation of where it came from and it seems undocumented... Care to share your knowledge? Kind regards.
Very helpful video, loved it. How to expand this to search a list of values but as a string pattern? Eg. In the list of value, one of it is "man", it returns "Human Resources". Thanks in advance.
If you download the Excel file below the video, on the worksheet named "1653 (an)" there is an example of search a list of values but as a string pattern in cell H19
Wow! While I was playing with some historical Space Data, you made this awesome video! This is a Majestic Beaut, right there! ;-) But as I commented on that Space Data video by Mr. Excel, I still wish FILTER would work in exactly the same way as the *IFS-functions (like SUMIFS, etc.) - I like their logic better.
You mean Mr Excel's video? Yes, that video is soooooooooooooo cool (in a spacey way)!!!!! I actually like that FILTER has to have an array calculation in the allow argument where we make a direct logical test against a column, rather than two arguments that have column and criteria. But I am probably WAY to biased because of my innate array formula glasses that I wear ; )
@@excelisfun well, the *IFS-functions allow for logical tests against columns, too. Of different length I might add (just like your example here). But of course, here FILTER is required for its report-like characteristics. I guess, its the difference in behavior that bothers me the most, rather than anything else. BTW: I solved all of Bills questions using functions only, maybe you’d like to head over there and check them out (in the comments). He made a little mistake in Q5, I think...
@@excelisfun I think also FILTER is more powerful as it is, SUMIFS could be a pain especially because they dnt allow array calc in their arguments. I wish sumifs and countifs syntax as filter function.
@@Excelambda Wow!!! I am so glad that you and Geert are on the Team, cuz I never even thought of that (wish sumifs and countifs syntax same as filter function).
Great XMATCH, FILTER power combo!!! Definitely is the best solution. For Fun , MMULT =LET(ec,G6:G13, c,et[ExpenseCategory]=TRANSPOSE(ec), mm,MMULT(--c,IF(ec,1)), FILTER(et,mm=1)) ...and your formula with top row added as a single cell formula =LET(ec,G6:G13,h,et[#Headers], f,FILTER(et,ISNUMBER(XMATCH(et[ExpenseCategory],ec))), r,ROWS(f),sq,SEQUENCE(r+1,,0), IFS(sq=0,h,TRUE,INDEX(f,sq,SEQUENCE(,4))))
Hi Mike. What if in the full table in the Expense Category column you have duplicate records, for an instance 2101 appears 3 times. How would you make a unique list of Expense Category codes in one column and the sum of the amounts in the other one? The solution should be dynamic. I am trying with FILTER but it does not work. I also have difficulties with SUMIFS.
Thanks Mike FOR THIS GOOD tricks I also have ONE suggestion to you that you also create written step PDF or words documents so one REFER to it in SHORT period of time. So ONE CAN TAKE complete advantages of YOURS efferts.thanks once again.
Since creating pdf notes doubles the time for creating videos, I only do it for my full class vireos at RUclips, like the Excel Basics, Advanced Excel Class, Data Analysis class, Advanced Data Analysis class, Statistics Class and so on. I am sorry about this. But, this does mean that there are about 1000 of the 3000 videos that I have posted that have notes.
AND... I am slowly getting used to thinking in the new formula way (which is so much easier), and so it is getting harder and harder to remember the old way...
xmatch isn't showing on my Office365 ProPlus version. Any suggestions? ProPlus should have most the advanced options I would think. Thanks for the great video.
Awesome, Another Filter solution is Countifs instead Isnumber+match =FILTER(et,COUNTIFS(G6:G13,et[ExpenseCategory])) but Countifs is slower And old school solution at N6:Q20 with unique PO no. =IFNA(INDEX(et,MATCH(0,INDEX(ISNA(MATCH(et[ExpenseCategory],$G$6:$G$13,))+ISNUMBER(MATCH(et[PurchaseOrderNo],$O$5:$O5,)),),),COLUMNS($N6:N6)),"") Ctrl+Enter
@@ExcelWizard you should do a video with xlookup, xmatch and binary matching. BTW the speed difference may be significant on a relative basis but for most applications you wouldn't notice. So I think this alternative that you and ce917 posted is very good.
Nice! A few other Teammates posted that one too. I like it cuz it is it bit shorter. Excel Wizard said it was slower. Any thoughts? I know years ago when I wrote Ctrl + Shift + Enter book, and timed a lot of formulas, COUNTIFS was notorious for slowness when we made array calculations.
@@excelisfun I would be interested to know the answer to that. My recollection, having been taken to task by Bob Phillips for formula inefficiency, was that the IFS family were blisteringly fast by comparison to the alternative SUMPRODUCT array solutions. I thought they were about a factor of 3 faster, which is just about the opposite of the findings you quote. Now, in the era of dynamic arrays, there might have been a further round of performance optimisation which could have changed the game. BTW I did scan previous replies but didn't notice that I was following in the footsteps of others without giving credit :-( I did like the way 'filter desired expenses' in the formula almost hangs together as natural language.
@@excelisfun I just did a set of range timing runs on 4 repeat blocks of your data and got 27.5 and 28 ±3 milliseconds for XMATCH and COUNTIFS respectively.
@@peterbartholomew7409 Thanks for timing, Peter! How did you time? I bet that the new Calculation engine is more better than the old one. I know Charles Williams, England Excel MVP, did a lot of testing and he said as much.
@@excelisfun I used Charles's Calc Range timing routine. I averaged over 6 repeats for each and the ±3 was based upon (max-min)/2 for each set. I hope there are no calculation settings I should be aware of, I simply went with the defaults.
Nice :) Before FILTER, the array formula is a "monster" to majority of regular users. Luckily, Excel has "Advanced Filter" to avoid that monster. Limitation: Not update instantly with source data. :( But should be good enough for most cases. ;p
Very cool, Mike. I was able to make this work perfectly for my tables. Question: I also tried to do this using a list of wildcard matches, and couldn't figure it out. Is there a trick that you know of? I can make it work with a single item w/wildcards, but if I try a list of items with wildcards, it won't work.
Here is the reverse of what you want (contains one item), even though you already said you had a way (EMT1653): ruclips.net/video/wDR5YcAcTx0/видео.html I'll have to think about a list of contains criteria...
Maybe something like this: =FILTER(fSalesAnswer[Product],MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(F17:F18),fSalesAnswer[Product]))),ROW(F17:F18)^0)) where F17:F18 holds list of contains criteria (two rows)
I feel like such a dumb****, I'm repeatedly getting the #VALUE Error. It's all good till ISNUMBER but applying the final filter function is giving me the error. Help please :( I need to use this functionality pretty much daily
![COLOMBIA vs. CHILE [4-0] | RESUMEN | ELIMINATORIAS SUDAMERICANAS | FECHA 10](http://i.ytimg.com/vi/TDjN4W3yy1k/mqdefault.jpg)
Mike, you read my mind!
I built the first formula with the multiple matches inside FILTER late last week. Shortly after that, I decided to go find someone who figured out how to Filter match a list this weekend. Sometimes I need to match 100 or more items from a table and need the table format.
Sunday, I get a notification and see that you posted exactly what I needed. I'll be using this one starting tomorrow.
Thanks again for all the great videos!
You are welcome for the fun with list filtering, M Smith!!!!
Thank you Mike, always easy to understand your explanation.
Glad I can explain easily for you, Eduardo : ) : )
My mind is blown! 🤯 This was exactly what I needed and you made it so easy to understand. Thank you!!
You are welcome!
Hi Mike, I looove your videos! 🤩🤩🤩 I've learned so much over the years, all my Excel is self-taught by watching so many of your videos and doing web searches for solutions and ideas, but you are a hero, and the best at that! Your style is unique, you always sound so friendly, and you have the best step by step tutorials. Thanks for the good work!
Hi Mike. This video really is so helpful across so many situations. One question, what if I want to sort my results by the Expense Category table?
Holy cow, you saved me so much time. It took me a second to apply, but now that I understand I couldn't be happier! thank you.
Yes, ISNUMBER/ISNA/XMATCH/MATCH has been saving time in analysis for decades : ) Glad this helps, Tiffany!!!
A practical application of the MATCH function. Thank you very much for sharing Mike. It is very useful!!
Yes indeed, MATCH and XMATCH are practical : )
Thank you!!! This is amazing!
ABSOLUTE BRILLIANT
Glad this helps : )
This saved me a lot of work and is exactly what I was looking for, you sir are a genius. Many thanks for sharing.
Thank you so much for this! I'm trying to teach myself data analytics, so I'm also teaching myself Excel. I've been beating my head against the wall for the past hour trying to figure out how to do exactly this--thank you so much for such a quick and accessible video!
Glad this helps, Iynne!!!
Thank you. I had 57 account numbers I needed from a list of thousands.
Perfect application!!!!!
The master of Excel formulas. Thanks Mike ..
Glad you like it K D !!!!!!
Awesome explanation, and crisp information, Thank you very much ..!!
You are welcome very much!!!!
Excellent! I’ve bought your new book. Very great one. Array formulas are so useful and easy to use. Thanks your great support.
Glad you enjoy everything! Thanks for buying the book : )
Mike!! ... AGAIN... MASSIVE .... THANK YOUUUU!!! 🙏🙏🙏🙏🙏🙏🙏 !!!! somebody asked me if it was possible to filter the graphs by two or three items or more (or based on a list) by the multiple selection from pivot table slicers...and this tutorial has the elegant solution! It's GOLD 🏆🏆🏆🏆 #GoTeam !!!!
Massive Rad Gold for the whole Team!!!!! So glad this helps, Brother of Rad Excel Recalc or Die : ) : ) : ) : )
@@excelisfun brothaaa of ExcelisFun who really makes Excel to be FUN !! 💥💥💥🚀🚀🚀
@@excelisfun I'm coming from the future, this logic still ROCK !!! but wait: PUNK ROCK!! 🤘🤘🤘!!
@@spilledgraphics I love you duuude!!!!! Logic Rock, FILTER Multiple OR Rocks and Puck Rocks!!!!!
Great explanation.👍
Glad it was so : )
Wow. I hadn’t paid attention to XMATCH before this tutorial. Some of my Excel applications suffer from sub-optimal solutions that XMATCH will solve. I look forward to applying this new knowledge. Thanks, Mike!
You are welcome, Jim : )
I was looking for this! You saved me! Thanks
You are welcome, A C!!!
Brilliant - this is exactly what I was looking to do! Thank you
You are welcome!!!
Excellent trick boss
Glad it helps!!!
This is an amazing way of approaching this issue! Thank you so much for your help! Big time saver
Glad this helps!!!!
Thanks Mike-- I just needed this and it brought me to your trusted channel 👍👍 - Im really not using XMatch enough, I feel its the "Lesser used" of the newer formulae!
Glad the video helps. We are all just getting used to all the new and wonderful functions in Excel : )
Mike, thank you for the tip. Huge respect for all your education activity. Take Care!!!
You are welcome, Filip!!
The bad news is I needed this last week....the good news is I will need this again next week. That was a great explanation. Thanks for sharing Mike....
The bad news is that I have done this trick about 20 times during my 12 years at RUclips (not all with FILTER function but always with MATCH). The good news is that I finally marketed it well , and FILTER is now in all of Microsoft 365 ; )
@@excelisfun But the better news is that to find the items in the short list that are NOT in the dataset, simply switch out IsNumber for IsError to return the Falses. Works a charm. Thanks Mike!
=FILTER(Table2,ISERROR(XMATCH(Table2[Part No],Table1[Part No])))
@@mattschoular8844 That is correct, SIR!!!! : )
This is absolutely a nuclear weapon for FILTER function. Thank you so much.
Yes it is!!! I love your term: absolutely a nuclear weapon : )
@@excelisfun weapon of mass construction indeed!!!
Still the undisputed excel champ! Keep up the great work :)
Just having fun with Excel : ) : ) Glad you like 'em, Kurt!!!
Totally undisputed !! thank you Mike !! 🙌 #GoTeam!! 🙌
Helping lives... Great work. You are getting paid in prayers...👍
And thumbs ups and comments, that is all I charge ; )
Thanks, this worked perfectly!
Glad it helps, C S G!!!!
You're a Beast, what a clear way to explain.
So glad that this helped, Gustavo!!!!
Even the older formulas were excellent time savers, but this one, this is Magic!
Thanks Mike
You are welcome, 7Kiya!!!
Mind Blowing! Thank you so much Mike!!!!
❤Superb. Short excel videos are quite informative
Thank you this works great! I do have a question when I copy and past the list into the “expense category “ In your case and it doesn’t filter results. However when I enter it manually it does filter the list.
What am I missing? Is it a setting issues?
Thanks, Michelle
Man you make great videos. I’ve been following you for about 6-7 years now. Thanks!
You are welcome for the 6-7 years of fun with Excel. Let's make it another 6-7 years - because there is so much in the New Excel : )
It would be so great to see a tutorial completely dedicated to comparing 2 columns for duplicates, matches, different sizes etc. and all the to do's and don'ts. A common task we all struggle with. Thanks Mike!!!
I have over 30 videos about comparing two lists and a whole playlist dedicated to just this topic. If you ever have a topic, just search my home page. Here is compare two list playlist: ruclips.net/p/PL83E0D7B674B73A06
OMG, I can soooo use that... Mike, keep up the awesome work...I always appreciate your videos.
You are welcome! I am glad you can use this : )
First solution I thought about was advanced filter :). Thanks for sharing this awesome formula and thumbs up!
Advanced Filter is still pretty good : )
Thank you very much for this amazing video
For me, this is one of the best examples of why M 365 Excel is so amazing. We used to build reports like this with very complex array formulas back in the day, but now? It is very easy : )
@@excelisfun yes I remember these old long array formulas and I learned them also from you. Index with Small function with If and row and rows for the K of the small function. U also were using aggregate to avoid control shift enter. I learned a lot from u Mike
@@nadermounir8228 That is it!!!!! You are very smart because you have the old and new : )
@@excelisfun Thanks to u Mike :)
Thks one more time Mike. You are a genius man !!!
You are welcome for the filter by list fun!!!!!
Thank you for amazing formula.
You are welcome, Idham!!!
Thanks Mike. Amazing how easy it became with the new functions!!! but the old schools was fun too.
Old School is getting tiresome since New School is so easy : ) But I will always remember the old school too.
Brilliant demonstration. Thank you very much
You are welcome very much : ) : )
Absolutely Amazing ... that's great Mike .
Glad it is great for you, Hussein!!!
Many thanks Sir very useful
You are welcome!!!
Mike, Really appreciated for your time & efforts making these videos! Very powerful formulas!
Yes, comparing two lists with XMATCH, or MATCH in the old days, is one of the more common tasks throughout Excel History. It is just made a lot easier with the new and amazing FILTER function.
Awesome, just awesome. I've been trying to do this for the past couple of months. Thanks for helping out. I am wondering if we can do something similar by matching just one data name to multiple instances and then populating a table with all those results?
thanks Mike.. this is exactly what i was looking for. I will try this tomorrow at work.
Glad it helps : ) : )
Excellent Work!!!!!
Glad you like the EXCELlent work!!!!!
Just got my mind blown! Amazing, thank you!
You are welcome for the mind blowing : )
=FILTER($A$6:$D$197,ISNUMBER(SEARCH($C$6:$C$197,CONCAT($F$6:$F$13))))
Thanks Mike, for your great explanation may be above also be works .
You are welcome!
But why CONCAT?
Thanks, that is what I was looking for.
Thank you Mike!
Very good advice as usual!
God bless you!
You are welcome, Ivan!!!
Simply elegant!
Glad it is so : ) : )
very nice, cool use of XMatch
Glad the XMATCH is cool for you and the rest of our Team!!!!
very nice and useful video sir. thanks.
further sir, can we use wild cards in include argument of filter function ? please help
Thank you Mike sir. Your excel videos help us to solve our daily problems which we face in our day to day work. Keep sharing as your video give us insight about excel. Thank you. 😊😊😊
Glad to help, Amit!!!
I not only like it, I love it!!!
Love and Excel go together like FILTER and fun ; )
@@excelisfun hahahaha, right
Great class!
Another way to get the result:
=MMULT(--(et[ExpenseCategory]=TRANSPOSE($G$6:$G$13)),ROW(G6:G13)^0)
Thanks for the more fun : ) : )
YOU ARE AMAZING!
Excellent !! FILTER are my favorite
Mike, you are great! This is awesome. I was looking just for this! Thanks a ton! 👍🏼
You are welcome a ton, Vijay!!!
Two were looking for this ! #GoTEAM !!!
Wow - great tip! Thanks for sharing!
You are welcome for the share : )
Another great one. Great little trick: if for some reason you want to exclude the expense category numbers just use ISNA instead of ISNUMBER
I’ve been using xmatch to check if multiple values exist or not in a range a lot more than the isnumber (which requires MMULT)
Yes, ISNA is great for the not in list!! Thanks, Patrick!!
Thank you sweet internet stranger! This is what I needed!
AMAZING! This was exactly, exactly what I was looking for.
Glad I could help!
Boom!What An Awesome Formula Simple Compared To Old School Really Impressive...Thank You Mike :)
Boom! You are welcome : ) : )
Hi , this is good!!! The other way to do this is by using the ADAVANCE FILTER tool.
Yes, Advanced Filter is Old School, but less efficient because it does not update. When everyone switches to Microsoft 365, like VLOOKUP, there will be very few cases were we would still use Advanced Filter. But maybe still a few : )
amazing work once again, very helpful for reports we need to make at work! :)
Glad it is helpful!
So needed this a month ago. Alas, I ended up using PQ to resolve my issue but this is a great formula Mike, thank you 🤗
PQ great, but if you need instant update, formulas are the way!!!!
@@excelisfun I sure didn't know about the 'arrow' at a table header/diagonal...BRILLIANT TIP !
@@paspuggie48 Ya, I love it too. I did not know about it for a long time, but when I learned it, I was just like you: Cool Wow!
Wonderful tutorial
Glad you like it!
Thanks for another great learning experience. Is it possible to do the same argument operation on two columns and then filter it to matching column headers? (Repeats in both columns)
im the first... wohooooOOOO.... thank you Mike for this amazing magic trick again!
You get the first place trophy!!!!
I've been searching everywhere for more info on array logic with MATCH (and XMATCH) to expand my skills, like what you just demonstrated using an array in the lookup_value, or using 1. I've seen these at work, but there's never an explanation of where it came from and it seems undocumented... Care to share your knowledge? Kind regards.
Assuming the list is in different sheet, would it be possible to filter the master table itself, so I can adjust certain values for instance?
great, thanks
You are welcome!
Oh man that is awesome! Thank you for sharing!
You are welcome for the share!
Wonderful video Mike 👍
Glad you like it, Sachin!!!!
Very helpful video, loved it. How to expand this to search a list of values but as a string pattern? Eg. In the list of value, one of it is "man", it returns "Human Resources". Thanks in advance.
Here is video for single cell contains condition: ruclips.net/video/wDR5YcAcTx0/видео.html
If you download the Excel file below the video, on the worksheet named "1653 (an)" there is an example of search a list of values but as a string pattern in cell H19
Nice trick Mike
Glad it is nice for you, Amit!!!
Wow! While I was playing with some historical Space Data, you made this awesome video!
This is a Majestic Beaut, right there! ;-)
But as I commented on that Space Data video by Mr. Excel, I still wish FILTER would work in exactly the same way as the *IFS-functions (like SUMIFS, etc.) - I like their logic better.
You mean Mr Excel's video? Yes, that video is soooooooooooooo cool (in a spacey way)!!!!!
I actually like that FILTER has to have an array calculation in the allow argument where we make a direct logical test against a column, rather than two arguments that have column and criteria. But I am probably WAY to biased because of my innate array formula glasses that I wear ; )
@@excelisfun well, the *IFS-functions allow for logical tests against columns, too. Of different length I might add (just like your example here).
But of course, here FILTER is required for its report-like characteristics. I guess, its the difference in behavior that bothers me the most, rather than anything else.
BTW: I solved all of Bills questions using functions only, maybe you’d like to head over there and check them out (in the comments). He made a little mistake in Q5, I think...
@@excelisfun I think also FILTER is more powerful as it is, SUMIFS could be a pain especially because they dnt allow array calc in their arguments. I wish sumifs and countifs syntax as filter function.
@@Excelambda Wow!!! I am so glad that you and Geert are on the Team, cuz I never even thought of that (wish sumifs and countifs syntax same as filter function).
@@GeertDelmulle You might like my proposed formula with COUNTIFS to build the criterion and FILTER to do the deed.
Great XMATCH, FILTER power combo!!! Definitely is the best solution. For Fun , MMULT
=LET(ec,G6:G13,
c,et[ExpenseCategory]=TRANSPOSE(ec),
mm,MMULT(--c,IF(ec,1)),
FILTER(et,mm=1))
...and your formula with top row added as a single cell formula
=LET(ec,G6:G13,h,et[#Headers],
f,FILTER(et,ISNUMBER(XMATCH(et[ExpenseCategory],ec))),
r,ROWS(f),sq,SEQUENCE(r+1,,0),
IFS(sq=0,h,TRUE,INDEX(f,sq,SEQUENCE(,4))))
Awesome!!!!! cr gr0912, you are so awesome : ) : ) Go Team!!!
MMULT is fun, but harder in this case ; )
@@excelisfun Indeed, normally I use it when sumifs or countifs do not allow arrays in their arguments. Thanks!!!😊👍
Hi Mike. What if in the full table in the Expense Category column you have duplicate records, for an instance 2101 appears 3 times. How would you make a unique list of Expense Category codes in one column and the sum of the amounts in the other one? The solution should be dynamic. I am trying with FILTER but it does not work. I also have difficulties with SUMIFS.
Amazing 👏 Thank you Mike :) 😃
You are welcome, Nader!!!
I ran into this use case earlier today. I should have checked my subscription feed ! Hah now I know :)
Now you know how to have more fun with this task : )
excellent work!
EXCELlent comment : )
I was looking for this for a week
Glad this helps!
Thanks Mike FOR THIS GOOD tricks I also have ONE suggestion to you that you also create written step PDF or words documents so one REFER to it in SHORT period of time. So ONE CAN TAKE complete advantages of YOURS efferts.thanks once again.
Since creating pdf notes doubles the time for creating videos, I only do it for my full class vireos at RUclips, like the Excel Basics, Advanced Excel Class, Data Analysis class, Advanced Data Analysis class, Statistics Class and so on. I am sorry about this. But, this does mean that there are about 1000 of the 3000 videos that I have posted that have notes.
Very slick Mike, great video! That old formula is a beast!
AND... I am slowly getting used to thinking in the new formula way (which is so much easier), and so it is getting harder and harder to remember the old way...
xmatch isn't showing on my Office365 ProPlus version. Any suggestions? ProPlus should have most the advanced options I would think. Thanks for the great video.
Replying to myself. It seems my online version has xmatch. Go figure why online and offline are different.
Great
Glad it is great for you, Atta!!!!
You are great sir
Glad I can help!!!
Awesome,
Another Filter solution is Countifs instead Isnumber+match
=FILTER(et,COUNTIFS(G6:G13,et[ExpenseCategory]))
but Countifs is slower
And old school solution at N6:Q20 with unique PO no.
=IFNA(INDEX(et,MATCH(0,INDEX(ISNA(MATCH(et[ExpenseCategory],$G$6:$G$13,))+ISNUMBER(MATCH(et[PurchaseOrderNo],$O$5:$O5,)),),),COLUMNS($N6:N6)),"")
Ctrl+Enter
Thanks for the alternatives, Excel Wizard!
How do you know it is slower? Did you time it?
@@excelisfun Yes, I time it,
Lookup, Match is a lot faster then Sumifs, Countifs
ruclips.net/video/hjpFIIy5Vpw/видео.html
@@ExcelWizard you should do a video with xlookup, xmatch and binary matching. BTW the speed difference may be significant on a relative basis but for most applications you wouldn't notice. So I think this alternative that you and ce917 posted is very good.
Beautiful!
Beauty with Excel is fun!!!!! Thanks for stopping by in the comments, Deepak!!!
Fantastic
Glad you like it!!!
Amazing. Keep it up
Glad it is amazing for you!!!
Awesome again😀
Glad it is so, again !!!
Hi Mike. Nice problem. Only a minor variation this time!
= FILTER( et, COUNTIFS(desired, et[ExpenseCategory]) )
Nice! A few other Teammates posted that one too. I like it cuz it is it bit shorter. Excel Wizard said it was slower. Any thoughts? I know years ago when I wrote Ctrl + Shift + Enter book, and timed a lot of formulas, COUNTIFS was notorious for slowness when we made array calculations.
@@excelisfun I would be interested to know the answer to that. My recollection, having been taken to task by Bob Phillips for formula inefficiency, was that the IFS family were blisteringly fast by comparison to the alternative SUMPRODUCT array solutions. I thought they were about a factor of 3 faster, which is just about the opposite of the findings you quote. Now, in the era of dynamic arrays, there might have been a further round of performance optimisation which could have changed the game.
BTW I did scan previous replies but didn't notice that I was following in the footsteps of others without giving credit :-( I did like the way 'filter desired expenses' in the formula almost hangs together as natural language.
@@excelisfun I just did a set of range timing runs on 4 repeat blocks of your data and got 27.5 and 28 ±3 milliseconds for XMATCH and COUNTIFS respectively.
@@peterbartholomew7409 Thanks for timing, Peter! How did you time? I bet that the new Calculation engine is more better than the old one. I know Charles Williams, England Excel MVP, did a lot of testing and he said as much.
@@excelisfun I used Charles's Calc Range timing routine. I averaged over 6 repeats for each and the ±3 was based upon (max-min)/2 for each set. I hope there are no calculation settings I should be aware of, I simply went with the defaults.
Nice :)
Before FILTER, the array formula is a "monster" to majority of regular users. Luckily, Excel has "Advanced Filter" to avoid that monster. Limitation: Not update instantly with source data. :( But should be good enough for most cases. ;p
Yes, Advanced Filter is till good : )
Very cool, Mike. I was able to make this work perfectly for my tables. Question: I also tried to do this using a list of wildcard matches, and couldn't figure it out. Is there a trick that you know of? I can make it work with a single item w/wildcards, but if I try a list of items with wildcards, it won't work.
Here is the reverse of what you want (contains one item), even though you already said you had a way (EMT1653): ruclips.net/video/wDR5YcAcTx0/видео.html
I'll have to think about a list of contains criteria...
Maybe something like this:
=FILTER(fSalesAnswer[Product],MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(F17:F18),fSalesAnswer[Product]))),ROW(F17:F18)^0))
where F17:F18 holds list of contains criteria (two rows)
Advanced Filter does it easily too.
@@excelisfun This works! - I don't understand it yet, but it works.Thank you!
I feel like such a dumb****, I'm repeatedly getting the #VALUE Error. It's all good till ISNUMBER but applying the final filter function is giving me the error. Help please :( I need to use this functionality pretty much daily
same here, my formula looks identical but getting value error.