VLOOKUP: Step Aside! New Epic Lookup Function = FILTER Dynamic Array Function (EMT 1521)
HTML-код
- Опубликовано: 29 окт 2018
- FILTER Function: One or More Lookup Values Return One or More Returns Values EMT 1521
Download Excel File: people.highline.edu/mgirvin/Y...
In this video learn about THE Most Amazing Lookup function for dealing with Multiple Lookup Values or Multiple Returned values when doing lookup! Learn about the FILTER Function to filter data sets, columns, or to do complicated Lookups.
Entire page with all Excel Files for All Videos: people.highline.edu/mgirvin/ex...
Full Lesson on these new Dynamic Arrays and new Excel Calculation Engine: Comprehensive Excel Dynamic Array Formula Lesson: The Power of Array Formulas (EMT 1516)
![MAF Teeski - Whole Thing (feat. VonOff1700) [Official Music Video]](http://i.ytimg.com/vi/SHWEj-ndz6g/mqdefault.jpg)
![Finesse2Tymes - Pretty Ricky [Official Music Video]](http://i.ytimg.com/vi/xIoP_mhYRT0/mqdefault.jpg)
This video is 5 years old, and yet it is the best video I could find on the FILTER function. The explanation is clear, concise, and makes the material seem interesting. Furthermore, the examples provided are very relevant. I recently started watching the videos on this particular channel, and the videos are so good that I actually ended up subscribing to this channel. Thank you, Mike and Team!
You are welcome!!!
A thousand pox on the individual that downvoted this video. You should be ashamed.
Thanks for the support, DaytonFamilyofFour!!! I think the person mixed up their right to criticize with their obligation to say thank you.
Mike, your multiple lookup feature of multiplying is exactly what I needed. Your videos are so easy to follow along with. Also, thanks for the way in which you construct your site to download your files.
Professional life savior! I LOVE YOU
Glad to help!!!!!
Always Excellent and ‘Amazing.’ Thank you.
I have to add another comment - those new functions are sooo beautiful!
Thanks for the "another" comment, Teammate : )
Sir, you are a great teacher,,, you are always amazing,,,, Love from Pakistan
Always glad to help!!!
Awesome....this recentl Dynamic Array series of videos have just earned it's very own watch again folder in my RUclips library. I cant wait for the rollout so that ExcelIsFunner .Thanks Mike....
Yes, sir!! Excel is Funner!!!! I have a few more videos this week for your new playlist : ) Thanks for the support, Matt!!
Filter - what an amazing function.... Thanks for sharing, Mike!
You are welcome for the share, Malina : )
I often learn from here, the tutorial is very easy to understand. thank you
Hi Mike, can't wait for Dynamic Array features to be available for everybody. life is easy now with this features :) Thank you so much Mike for always inspiring us with your amazing videos. Keep it up and more Excel Is Fun in the future :)
Yes, I will keep it up!!!! Thank you for watching and supporting, edgie!!!
@@excelisfuny the way, Im ED Cabading... I used to send you some solutions to some excel problems. This Dynamic Array would be my favorite now. Thanks again Mike
@@edgiedapogi4848 , Oh Ed!!!! Great to hear from you! Yes, these new dynamic arrays are so useful and helpful - many more videos to come : )
This will change my life! I'll be able to save hours using this function alone compaired to the formulas I've had to construct that were almost as long as a Ludlum novel (and just as bloody). Thanks for the tutorial Mike, awesome as always!
Yes, I soooooooo agree : ) FILTER to the rescue!
Thanks for your support, David Manhire, on these videos that you learn from with your comments, thumbs up, and of course your Sub. It means a lot to me and helps grow the channel so that more people can find this free education : )
Thanks Mike. Great Functions. Thanks for the Videos!!! :)
You are welcome, John!
Hi Mike this video is totally amazing. I found that in the 1st example of 1521 which is about filter by two drop down list, this way also works =+FILTER(LookupTableAnswer;IF(ISBLANK(K5); *LookupTableAnswer[Customer]=LookupTableAnswer[Customer]* ;LookupTableAnswer[Customer]=K5)*IF(ISBLANK(L5); *LookupTableAnswer[Customer]=LookupTableAnswer[Customer]* ;LookupTableAnswer[SalesRep]=L5);"None")
just this part in bold are different. but i appreciate so much your method.
Hi Mike.. more magic from the master.. excellent! Thumbs up!!
Thanks for the support, Wayne!!!! There is another FILTER Function coming out in a few days that has some more cool tricks : )
it was GREAT Mike Thank you so much!!!!
You are welcome so much! Thanks for the support, Sevag!!!!
Wow. That's pretty awesome. Thanks Mike.
You are welcome, Syed!!! Thanks for your pretty awesome support : )
Thanks for these great tips Mike :)
You are welcome, K B !!!!!
Awesome lesson. Thanks Mike!
You are welcome, Keisha !!! Thanks for your support!
Excel becomes more fun with the new eng... thank you so much Mr. Mike
You are welcome, Ismail!!! I agree with you about the fun : )
Awesome video Mike! Excel rules!!
I agree, Chris : ) Excel Rules!!!!!
Great video!
I love just how QUICK this is.... Not only to create the formula, but to calculate the data-set and return the answers. Whereas with some 'array' formulas, it can sometimes take a few seconds to calculate - not a long time, but noticeable.
There is no doubt that the New Dynamic Arrays transform how we do things in Excel. So amazing!!! Thanks for the support, Ash!
So so good!
BRILLIANT Mikee...... Excel isn't the way it was since lonnggg.... Its grwoing fastttt.... Veryyyy Fastttt
Yes, given that we have Power Pivot, Columnar Database, Power Query, and now Dynamic Arrays and a new Excel Calculation engine, Excel is better than ever : ) Thanks for the support, RRR!!!
Inspiring as always!
Glad it is is inspiring for you, Roman!!!
NICE! I need it this, thanks!!
Happy to help!
Awesome as always
Glad it is awesome for you, Dmitry!!!!
Thanks a lot!
Hi Mike, I really love your tutorials and I have been using them for the last few years. I having trouble trying to work out a solution for the following: I am trying to create a spreadsheet to track all of my jobs. I want to be able to have a master tab with all jobs on it and then several other tabs which extracts the all of the data from the row to the relevant table. I am unsure on if this is possible. Any help on this would be great. Many thanks in advance Ian
love it :)
Very good lesson!!! Thanks :-))
Thanks, O Masterful Poet : ) : )
mind = blown
what a super powerful function
Indeed : )
Awesome sir ji
You are not Mike You are Mike Tyson Unbeatable Love from India.. enjoying your every single video.
Amazing !
Glad it is amazing for you, Marcos!!!
the Download Excel Files are a great!!!!; very good job Mike👍👍👍👍
You are welcome for the download files, DIGITAL COOKING!!! In every one of the 3,200 videos I have posted all the way back to Feb 2008 when I posted my first vidoe, I have provided the download files for better learning : )
@@excelisfun yes! Indeed i saw few of them they are well presented and to surround the ideas and technics we should practice them thanks Mike
@@DIGITAL_COOKING You are welcome! Thanks for being such a dedicated viewer with amazing support : )
this is a great and useful video, i tried it and it works for my HR list. I had a problem to extract the photo of the employees in the spilled table. please let me know if it is possible to add the photo to your table and show it in the filtered spilled table.
Amazing .... what a revolution that Excel making now
Yes, it is a revolution!!!! Thanks for the support, Hussein!
No....Thanks to you .... for all the learning process you making to us
@@HusseinKorish You are welcome!!!
Excellent sir
Glad it was EXCELlent for you, GS!!!! Thanks for your support : )
Amazing, even with the great new functions though the formulas can soon turn into an encylopedia... Excel wouldn't be fun without them.
It already is an encyclopedia : ) There are too many for one one person to know it all - that is why it is so lucky that we have an Online Excel Team with so many eyeballs to try and figure cool new things and contributing to questions and discussions : )
Fantastic
رائع 😙
Glad it is fantastic for you!!! Me to : ) Thank you for the support, Fuad A, with your comment, Thumbs Up and Sub : )
Veryyyyy nice 👍👍👍👍👍💐💐💐💐💐
Thanks for the many Flowers and Thumbs Ups, and for your support, ARUN!!!!
Mike. My guess is that the use of SEQUENCE to create an array on 1s is unnecessary. I suspect that the condition
( ISBLANK(Customer) + (LookupTable[Customer] = Customer) ),
where 'Customer' refers to cell H5, would broadcast the TRUE to every record with '+' acting as an OR operation.
That is absolutely B-E-A-U-T-F-U-L!!! The PB Method! Can't wait to make a video about that shortened version, nicknamed PB Method : )
Just added a sheet to the downloaded workbook with your example. Sheet '1521 PB' Thanks, PB!!!!
I was thinking along the same line. From Mr Excels pdf of these new formulas. “A Simple Broadcasting Example
Say that you have a 3-row-by-1-column array. Multiply it by a scalar or a single-cell array, and Excel will make copies of the 3 in the second array and fill the rest of the newly resized array with 3s. When Excel does the multiplication for =SUM({1;2;3}*3), you get 18.”
@@excelisfun WOW!!! My first published modern array formula -- without even having access to dynamic array functionality. THANK YOU for 'road testing' the modification to your formula (OK, strictly speaking it is more your formula than mine). It does seem to exhibit the simplicity that I have always sought from array formulas though.
@@peterbartholomew7409 Love it! Simplicity is great. And, yes: it is cool that REALLY Smart guys like you and Bill Szysz can invent formulas in versions that you don't even have!
Thanks sir
You are welcome, Mohsin!!!! Thanks for your support : )
Thanks once again, one question what if instead of one player we have a list of players, and also a list of dates and a third list with multiple matches? Is it possible to combine the previous in a sumifs, or sumproduct and sum the matched total? I am trying to find a solution? I try to read your excellent book cse, abut did not managed yet. Can you offer a solution to this problem?
thanks you
You are welcome, Enes : )
hi, nice video.... how do i select a specifc value in sub table filterd? all cells in column sub tables show the first line formula
If you want only the top 3 Sales for Amazon how would you do it? I've tried large but it evaluates all rows and gives the top 3 not just the ones for Amazon. Any ideas?
As you say Mike "Amazing!"
Yes it is! Thanks for watching and supporting, Roberto!!!
This new calc engine is the most significant and important change in Excel since......?
Well... Some other significant inventions: PivotTable in 1990s (really Improv from Lotus) , Power Pivot and Columnar Database in 2009, and Power Query 2013.
For me it is the most significant change since the invention of the original electronic spreadsheet. I probably wouldn't feel that way if I had significant volumes of tabular data to analyse but, for building models, the new functionality offers a way forward that allows one to sweep away the tacky, end-user-computing assumptions (single-cell formulas, reference by location using the A1 notation, relative referencing) that has often made spreadsheets the subject of scorn or at least condescension from IT professionals.
It is not just a question of a few superficial tricks and some new functions, however good they may be; it is an opportunity to change the way spreadsheet solutions are built, from the ground up.
@@excelisfun hehe...PQ is available from february 27, 2013 ;-)) Here is the link to the first announcement
blogs.msdn.microsoft.com/dataexplorer/2013/02/27/announcing-microsoft-data-explorer-preview-for-excel/
@@BillSzysz1 , You are right! Wow that is a cool link fro the past : )
I believe this version it will run faster on large data, right? :D
"Is it magic? Is real?" Amazing! Thanks Mike!
Yes, I tried a few bid data things and it worked faster. But, more importantly, Charles Williams, Excel MVP who is THE expert in evaluating the Excel Calculations Engine, says that it really does run fatser. He has a great tool that allows you to time formulas, and he has done some timing and says it is much faster : )
Could it filter by using wildcard? (E.g. column=“*”&Cell&”*”)
Thumbs upppp
Wawww Microsoft mean it by making dynamic arrays exclusive for Office 365 We really needs theses dynamic arrays to save more times
Thanks for the Thumbs Up, Mohamed!!! Yes, MS does mean that 100%: the only way to get the New Calc Engine and Dynamic Arrays is to get Office 365. That is what they said.
I do not yet have access to these new Dynamic Array formulas. But I use Advanced Filter a lot and it would appear that I can do what I do as efficiently if not more efficiently with the Filter function--and without creating objects which can clutter the excel file. Do you see situations where Advanced Filter is preferable to the Filter Function or situations where Advanced Filter can do tasks that cannot be performed by the new Filter function? Also Advanced Filter often used for unique records but Unique should be better for that. Actually I can usually get my unique lists from copy/paste from a dimension table and if not I use Power Query but from now on I am sure I will use Unique. Great Video!
Yes, I think that there are still situations where it would be easier to create the complicated logical tests with Advanced Filter than with formulas; but FILTER certainly makes the final extracted data set change instantly when formula inputs are changed, and that is pretty tempting : ) Thanks for your support, Robert, with comments, Thumbs Up and Sub : )
Thanks. I always do thumbs up and I am a sub. And I appreciated your tip on creating a unit vector with sequence and the zero step increment--I will definitely find many uses for that. I agree with you that Filter may be the favorite function, but I think Sequence could be the sleeper--many uses!! I am editing because I just read the post about the PB method and not needing the unit vector in this example--but I am glad you showed this trick for use In other excel situations where a unit vector is needed.
@@richardhay645 , Yes, SEQUENCE will have so many uses because we often need a pattern of numbers, and there are so many patterns! Thank you very much for your support, it really helps!
marry X-Max and happy new year.
Dear i want to know that can we extract data of our choice from an email and fill out it an excel row ?
Mike can u make a video as to how to go about your videos. I mean from where to start and where to end...that would be of great help for viewers... And mike u r a god of excel.. Respect from India...
ruclips.net/video/vKSAgdIKboY/видео.html
when will have the filter function available in 365 ?
Hi can you make DAX studio videos
Thank again for this amazing video and is it possible to use filter with * to find criteria contain some word?
Like this: =filter(table,column="*criteria*")
I couldn't find a way for this issue.
Yes, I think you can use the MATCH function with a wild card for the column number in VLOOKUP.
@@excelisfun it didn't work with match but i could do it with Find and Isnumber.
Thanks anyway, most of the things that I know have learned from you.
@@Softwaretrain Awesome that you used FIND and ISNUMBER!!!!!
Mike sir I didn't understand here why you use sequence function 6:02
This is awesome indeed. It's like a combination of vlookup and sumifs (if used in a sum function). In fact it's better because it has the added benefit of the empty clause too.
Can I ask a favour? I added a comment to your previous video, and I wondered if you could check it for me. It was an alternative to the formula you showed to add summation of values with totals. I'm intrigued as to whether it works or not and I don't have new excel to check! This was it below (I added the TYPE() check for date and revenue columns). It felt a little more simple than the sequence formula (which is obviously much cooler in a cool formula sense).
=SUMIFS(fRevenue[Revenue],INDEX(fRevenue,,MATCH(I4,fRevenue[#Headers],0)),IF(MAX(ROW(H10#))=
ROW(H10#),IF(TYPE(H10)=1,">0","*"),H10#),INDEX(fRevenue,,MATCH(I5,fRevenue[#Headers],0)),
IF(MAX(COLUMN(I9#))=COLUMN(I9#),IF(TYPE(I9)=1,">0","*"),I9#))
Very cool formula!!! I could net get it to work... I kept getting an error. This is why MS needs to deploy the new Array Features to everyone, so that you are not denied the opportunity to try this for your self. Can anyone else get this to work?
@@excelisfun I just realised after I wrote my latest comment that I could just simply sign up to the insider program on my laptop with another subscription and update to the latest package.
I downloaded your workbook previously, and now I have managed to get the formula to work. Here is a picture of it in the link below in your workbook (working!). I can send the workbook to you if you like, is there an email address at your website?
tinypic.com/r/nz3ts/9
Hi Mike. Not sure if you have done a video on this already but I wanted to use the new FILTER function do the old advanced filter trick where your source data has 5 columns but your filter criteria headers represent only 3 of those columns. I came up with: =INDEX(A2:E5,TRANSPOSE(SEQUENCE(1,COUNTA(A2:A5))),MATCH(J1:L1,A1:E1,0)). Is there some obvious easier way I am missing?
A1:E5 is my source data including headers
J1:L1 are the column headers I want to keep
the formula lives in J2
Can you send me workbook with your example and question to excelisfun at gmail?
Mike. One Question: can you use i.e ((Range = Criteria 1)+(Range = Criteria 2)) for the OR instead of using the Sequence? I know that using the Sequence was great to see the new function, but will the filter work just the same?
You could build an OR Logical Test within FILTER. But that is not what we did here. The logic of the formula in the video gave four options: 1) Something from One Column, 2) Something from a different Column, 3) And AND Logical Test based on two columns, or 4) just show all the records.
This: ((Range = Criteria 1)+(Range = Criteria 2)) would run an OR Test, and show records with one, or the other, or both.
However, if you look at Peter Bartholomew's post, there is a way to construct an OR Logical Test to make it work... : )
Must watch the video again :)
Mike, I need your advise, as to which office 365 version should I buy online, Office 365 personal or Office 365 home. Secondly, I also request you to please advise Microsoft to have an append totals (horizontal, vertical) for sums, averages, and other statistical functions. Your vdo are always great, whatever I have learned, it is only from one source, EXCELISFUN. The best source for serious excel user.
I do not work for Microsoft. Microsoft doesn't listen to me when it comes to features. I have said a number of things over the years, especially insistent about changing how the Histogram Chart works, but MS doesn't listen to me at all...
I think that they both are fine: Office 365 personal or Office 365 home. You just need to then sign up for Insider.
@@excelisfun how to sign up for insider?
@@excelisfun Microsoft should listen to you, as you are the master crafter of Excel, and know complete inside out of excel.
@@Al-Ahdal , If you have Office 365, then when you are in Excel you can go to the File menu, then to Account, then there should be a button to join Insider. Here is a web link for information: products.office.com/en/office-insider?tab=Windows-Desktop
HI, is there a way to FILTER by a LIST of items (Criteria), instead one or two values?
Sure. For an OR logical test on a single column, rather than:
=FILTER(et,(et[ExpenseCategory]=G6)+(et[ExpenseCategory]=G7)+(et[ExpenseCategory]=G8)+(et[ExpenseCategory]=G9)+(et[ExpenseCategory]=G10)+(et[ExpenseCategory]=G11)+(et[ExpenseCategory]=G12)+(et[ExpenseCategory]=G13))
use:
=FILTER(et,ISNUMBER(XMATCH(et[ExpenseCategory],G6:G13)))
where, et is table and G6:G13 has criteria.
when this function will be available to everyone
It is only in Office 365, and will be available in all Office 365 versions by early next year. That is what Microsoft says.
thanks for cool trick
i can't understand when we use * or + in include argument
i know it's not new but i never understand it beforer
It comes from Statistics.
* = AND Logical Test.
+ = OR Logical Test.
@@excelisfun do you have video explain that with examples
@@MohamedAlyCLAY Here is one: ruclips.net/video/2kvPdv_nvbM/видео.html
Videos #27 and #28 from my statistics class at Highline college:
people.highline.edu/mgirvin/AllClasses/210Excel2013/Ch04/Ch04.htm
Isn't this the same as running an advanced filter and building a macro to reset the data with new criteria the user enters? Downside is that advanced filter doesn't use exact cell info. i..e if I have Oranges and Oran as data and my criteria is Oran, it will pull both Oran and Oranges data. Do you know if any changes are coming to advanced filter?
The difference (and the #1 advantage) that formulas have over every other feature, is that when inputs change, formulas update instantly. For decades when we create data analysis solutions that has always the trade off: usually more complicated to create formula solutions but if the solution requires that it update instantly, then we must use formulas. Advanced Filter is really a great tool that many people do not use, especially given that earlier in this decade MS changed how Advanced Filter works when we re-run the filter (it used to not clear the data before running the new one). Advanced Filter can allow us to create very complex logical tests that are often much easier than formulas. I don't know of any changed coming to ADvanced Filter. BTW, you can do an exact match with Advanced Filter by using an = operator, like: =Orange. Enter it like: '=Orange
@@excelisfunThat worked, thanks! I created a named range with everything having "=XXX", which was then pulled in as a list to the Data Criteria to be used in the advanced filter.
: )
There are two groups of excel users--those who use Advanced Filter and those who SHOULD. The Full Outer Join of these two groups should watch Mike's Excel 2016 Class Playlist Video 19 for a very comprehensive lesson on Advanced Filter. In it Mike provides, among many other topics, a very clear answer to the critical issue this viewer raises.
@@richardhay645 I love this: "two groups of excel users--those who use Advanced Filter and those who SHOULD"... but then you get even more clever with this: "The Full Outer Join of these two groups should watch Mike's Excel 2016 Class Playlist Video 19"... I totally agree, without Advanced Filter, we WILL be less efficient in Excel.
apparently i think the excel on my laptop doesnt have this - what do you advise about where to buy the correct application with preloaded functionalities in 2021?
The ONLY version Microsoft sells with this is Microsoft 365.
Sir, for your kind information, after lots of trying, I've found a formula that helps solving this problem and dealing with these merge cells but when the destination data and their format are as same as source data. But if I change the destination data and their format, it won't give the right result. I'm attaching this finished file to your google+ account. Check this file, you'll understand everything. Please reply me sir. I'm your die hard fan.
Here is a great Excel question site that is great for back and forth dialog to get your Excel solutions: mrexcel.com/forum
Sir, will you please upload a tutorial on how to deal with merge cells while doing 3 way lookup? Suppose, there are 10 students (For example: A, B, C, D, E, F, G, H, I, J) and everyone appears for English, Math and Science exam. All the 10 students have given 6 semesters. 1st, 2nd, 3rd, 4th, 5th and 6th semesters are written on the top of every 3 subjects and in merged cells. Now, if someone wants to know, what are the scores for "A", "D" and "J" in every semester? I've already attached a screenshot of this problem to your google+ account. There I spelled "Semester" incorrectly. Kindly reply me and upload a tutorial on this as soon as possible. I'm waiting for your response....
I am sorry but I do not have a video on that, and currently I have a six month backlog of videos to make for my college. No problem, though, here is a great Excel question site that is great for back and forth dialog to get your Excel solutions: mrexcel.com/forum
Maybe they should have named it MULTILOOKUP instead of FILTER to make it easier for us old-timers to remember what it can do ;-) Thanks for the great video!
You are welcome, Rob! Thanks for your support : )
How can we update our excel 2019 version to 2021?
MS only offers it in Microsoft 365 Excel.
Sir notice, here I can solve the problem where semester are all sequentially placed one after another. But, if I change the position of one of these semesters, this formula won't give me the correct answers. Kindly check the screenshot and the file, I've sent on your google+ account and upload a tutorial on this as soon as possible.
Here is a great Excel question site that is great for back and forth dialog to get your Excel solutions: mrexcel.com/forum
3:47 FILTER Dynamic Array Function - Constants
Hello sir! How can I reach for you through email? or somehow a chat?
Wonderful video but regret one has to have office 365. Anyway cheers :)
I regret it too. MS made the same mistake when they invented Power Pivot. They only had it in some versions... It is just really bad that they offer these great features and don't give them to everyone. They are incetivizing everyone to get Office 365. And sooner than later, they will not offer anything but Office 365.
Sorry about the inconvenience, but thank you very much for your support : )
1
First!!!! Thanks, Hokhin!!!
Hi Mike!
Dynamic arrays will be in offices 2019?
p.s.: your channel is wonderful!!!
MS say they will only be in Office 365. I am glad that the channel is wonderful for you! Thank you very much for your support on each video : )
What's wrong if we use pivot table with its filter. It's much easier than Dynamic Array 🏋️♂️
I am not sure what you are trying to communicate, Lofty? Can you re-state? I am always interested in what you have to say!
@@excelisfun
Using filter in Pivot table can resolve this problem easily.
Please create a new playlist for these new categories (Dynamic Array), you go very fast while we still smelling the functions not eating it.
@@LotfyKozman O, Yes!! You are correct : ) BUT... for some solutions there is the requirement that the report updates instantly when the source inputs change, and that is where formulas come in. This is always been the case: Formulas are usually more complicated, but they are the only feature that updates instantly when inputs change. But yes, for ease in making, PivotTables are the way : )
@@LotfyKozman I have already created a new playlist. It is already featured on the front of the excelisfun channel at RUclips. It is the fourth playlist here: ruclips.net/user/excelisfun
😜😜😜🙃👋💥
Yes!!!!! Thanks, Thierry!!!!
Sorry Mike - Im not an "Insider" so your video is worthless to me.
"Microsoft has only made this new functionality available to Office 365 subscribers on the Insider channel. The updates will be rolled out to all Office 365 subscribers at a future point,"