Don't forget: ChatGPT often does not know what it is doing when you ask it to create a formula in the worksheet, M Code or DAX. I asked it: "Excel formula to extract records based on a list of items?" and it gave me: To extract records in Excel based on a list of items, you can use the VLOOKUP function in combination with the IFERROR function. Here's an example formula: =IFERROR(VLOOKUP(A2,$D$2:$E$6,2,FALSE),"Not Found") In this formula: A2 is the cell containing the item you want to search for $D$2:$E$6 is the range of cells containing the list of items and their corresponding records 2 is the column number in the range that contains the records you want to extract FALSE specifies an exact match This formula will return the record for the item in cell A2 if it exists in the list, or "Not Found" if it doesn't. You can drag this formula down to apply it to all the items in your list. lol or : ( Then I asked it: "Excel formula to extract records from a table based on a list of criteria?" It gave me an AND Logical Test rather than the correct OR Logical Test, as shown here: To extract records from a table in Excel based on a list of criteria, you can use the FILTER function. Here's an example formula: =FILTER(Table1, (Table1[Column1]=A2)*(Table1[Column2]=B2)) In this formula: Table1 is the name of your table Column1 and Column2 are the names of the columns in your table that contain the criteria you want to filter by A2 and B2 are the cells containing the criteria you want to filter by This formula will return all the records in your table that meet both criteria specified in cells A2 and B2. You can modify this formula to include additional criteria by adding more conditions inside the parentheses, separated by an asterisk (*) for AND conditions or a plus sign (+) for OR conditions. For example, if you want to filter by three criteria, you can use this formula: =FILTER(Table1, (Table1[Column1]=A2)*(Table1[Column2]=B2)*(Table1[Column3]=C2)) This formula will return all the records in your table that meet all three criteria specified in cells A2, B2, and C2. lol or : ( Then I asked it: "Excel formula to extract records from a table based on a list of criteria compared against one column in the table?" I got: To extract records from a table in Excel based on a list of criteria compared against one column in the table, you can use the VLOOKUP function in combination with the FILTER function. Here's an example formula: =FILTER(Table1,ISNUMBER(VLOOKUP(Table1[Column1],A2:A6,1,FALSE))) In this formula: Table1 is the name of your table Column1 is the name of the column in your table that you want to compare against the list of criteria A2:A6 is the range of cells containing the list of criteria you want to filter by The VLOOKUP function looks up each value in Column1 against the list of criteria in A2:A6, and returns a match if found The ISNUMBER function checks whether the VLOOKUP result is a number (i.e. a match was found) The FILTER function returns all the records in your table that have a match in the list of criteria This formula will return all the records in your table where the value in Column1 matches one of the criteria in the list. You can modify the formula to compare against a different column in your table by changing "Table1[Column1]" to the appropriate column name. You can also modify the formula to use a different range of criteria by changing "A2:A6" to the appropriate range of cells. This is pretty close. I wonder if it misinterpreted one of my videos and used VLOOKUP rather than XMATCH lol
Mike, you are the best. FILTER is my #1 goto function. It is an awesome function that does so much, and you make it do more (in a simple way) Job well done Mike.... =REPT("👍",1000000)
That is always my goal: tell a story with good details and good order to the details. The details and story should anticipate potential questions and answer them before they need to be asked : )
@@excelisfun ‘fi’ is an abbreviation for ‘fidus’ (as in ‘fidelity’), The expression means “forever loyal”. It’s the slogan of one of the US armed forces, if I’m not mistaking. The message I meant to convey was: we follow your YT-channel and always will. :-)
You are clearly the best teacher ever on excel ( at least on youtube) .. thank you , my excel guru and because of you people call me excel guru... .. have been following you since 2009
That's a great formula!!! I never remember to do that because in the old days MATCH functions was always faster than COUNTIFS, especially for array calculations. I have not timed the two since the new calc engine came out. I wonder...
@@excelisfungreat video Mike, hope you are well. Like you, I assumed countif would be slower. I don't think anything would have changed in that regard with the new calculation engine. I like xmatch. Although I'm surprised that they didn't include an option to put in a value for unmatched items when they released that function.
@@ricos1497 I did extensive timing for my 2012 book, and COUNTIFS was sometimes horribly slow - like blow your computer up slow. But I have not done extensive timing tests in the new calculation engine. I know that Excel MVPs Charles Williams and Mr Excel did some time and things were faster. I really should just go and create a big data set and time this... Interesting note: in my timing experiments the speed of calculations was always dependent on the combinations of functions and formula elements used - and this is because each function is programmed to work in a different way - so sometimes a certain combination would just not be efficient or effective - and others would work like a charm together.
@@excelisfun interesting. I always assumed that it was slow because COUNTIFS always referenced a range rather than an array, but I think that's the VBA programmer in me (passing a range of values to an array results in far quicker run times). I'd assumed that would still be the case, but probably not.
You know how people will argue about everything? Well in this case I would have approached the problem exactly the same way. Maybe after watching all your videos you have me trained!
I gave up QuickBooks for Excel over a year ago. That would not have been possible without DAs. All reports are based upon a Journal in which all transactions are recorded. My Journal and reporting models are built around FILTER,XLOOKUP,XMATCH,CHOOSECOLS,VSTACK,HSTACK and SUMIFFS. So far I have 6 instances of this exact construction in my report generators. My reporting model is still being fleshed out QuickBooks RIP!
Mike, great insights! It always amazes me others provide other solutions. We all know there's usually more than one way to do something in excel. Why do these people need to challenge you!
Thank you Mike. You work is amazing, I've learned the FILTER function from you and it made a very complex task easy and fast. Thanks to you I am the Queen of Excel at work. Your videos are increadibelly informative. You are my hero.
That sure beats putting a set of arrays added together in the 'include' argument. With that method, the list cannot be expanded or contracted without lots of editing. So, good show!
Thanks for posting yet another amazing video on Excel! Since I started learning Power Query just three weeks ago, I am trying to use it everywhere possible. 😎 I was able to develop a solution delivering the same results as shown in the video using PQ to populate the filtered list of Student-Class-Grade (cols $I:$K) using a Students table INNER JOIN Classes table merge query. I also used PQ to get a unique, sorted list ("Unqiue_Class") of the values in column D of the main table. I used the formula "=INDIRECT("Unique_Class")" to populate the data validation dropdowns for the cells in the Classes table in column G. Thank you kindly.
And... we can do vertical FILTERing by column names in I4:K4, although not very convenient and "static" (TRUE/FALSE for all columns) =FILTER( FILTER( Grades, ISNUMBER( XMATCH( Grades[Class], Classes[Classes]))), ISNUMBER( XMATCH( Grades[#Headers], I4:K4))) which became more comfortable and "dynamic" (python-like list with the possibility of repetitions and backward indexing) with CHOOSECOLS. So, we can store column names in table/spilled array... kind of "meta" level :) =CHOOSECOLS( FILTER( Grades, ISNUMBER( XMATCH( Grades[Class], Classes[Classes]))), XMATCH( I4:K4, Grades[#Headers])) maybe should have used LET for readability :)
@@viktorasgolubevas2386 Great point! I have doen videos about filter by column with two FILTER, but that was before CHOOSECOLS came out. It seems that maybe CHOOSECOLS might be an easier to read formula?
You can use the CHOOSECOLS function inside the array argument of FILTER, like: =FILTER(CHOOSECOLS(E3:G36,1,3),ISNUMBER(XMATCH(G3:G36,K3:K5))) or double FILTER, like this: =FILTER(FILTER(E3:G36,ISNUMBER(XMATCH(G3:G36,K3:K5))),{1,0,1})
Very good tip!!! As I said below: I never remember to do that because in the old days MATCH functions was always faster than COUNTIFS, especially for array calculations. But now, maybe the new calc engine will provide a new perspective...
Ooooo, I thought you were asking about the Table, not the cell with the data validation. Yes, as Cloin says, it is data validation. Here is a video if you would like: ruclips.net/video/fyPA9NK1eX0/видео.html
There is a way in older versions, but it is MUCH more complicted M 365: ruclips.net/video/TkaFVur8Nic/видео.html This is perhaps the best example of how M 365 is so much better than any other version in history.
@@excelisfun With reference to this example , if column D was serial numbers and column G is a list as it is . Is it possible to combine search , xmatch and filter to produce the report you have made?
Your channel is really useful : GREATLY APPRECIATED ! Many years already :) Have you got any trick to enclose the OR function in a match formula ? I'm looking to OR this match OR this match etc in the MATCH formula part... if the OR function only returns only TRUE or FALSE, which formula to use in order to get the same
As I mention a few other times in this thread: I never remember to do that because in the old days MATCH functions was always faster than COUNTIFS, especially for array calculations. But I wonder how they compare with the new calculation engine...
I had a problem where i was trying to extract data from a shared cloud based excel file using filter and tried a few other functions. They just returned nothing, not found an explanation why they don't work. If i looked at the file locally it all worked as expected.
This may sound like a dumb question, but have you tested to find out if the cloud file has to be open for the formula to work? Many functions don't update unless the target file is open - even if it is shared in the cloud. Also, are you sure the reference to the shared cloud file is correct? I've seen weird cloud setups that require a tweak to formula path or PQ code.
@@msmith3090 Not at all. It was a while back so can't 100% remember but i believe it would of been as i would of been working on both files at the same time. I was aware about a target file having to be open as have another one that's shared as a live stock list that's referenced to another master workbook which only updates if its open. If i direct reference to the cells i want to pull from the master workbook it works. Using CHOOSECOLS or FILTER just returns blank. I think i tried another function as well but i cant find what i tried looking back. If the file is local say in the same directory the lookups work as expected. It breaks when its in the cloud unless using direct cell referencing. When I'm deep in excel again I'll revisit it as I'm still learning new better ways to do things as i go along.
Hi, I need your help. I want to calculate cash amounts between two dates for individual clients from a drop down list, so if i choose a client from the drop down list and then choose the date range it will just show that figure for that person only. I know how to sum between dates but not using a drop down list to do it.
Nothing. So far CHATGPT can not write correct Excel worksheet, M Code or DAX formulas. Try it. Some of the formulas it suggests are not only wrong, but if they calculate correctly, they are terribly inefficient. Remember: that chat thing searchs the internet and much of the Excel and Power BI calculation stuff is junk. Especially Microsoft Help... However, it can zero in on close stuff if you ask the right question. But often, the person asking the question is not equipped to decipher if the result is accurate or efficinet... Look at the post I pinned to the top of this comment section.
Here's my issue with the filter function. It makes the excel spreadsheet so large that I can't email or share it. You can't even upload it to a Google Drive to share because it's so large, not to mention that it takes a much longer time to open up. I have a spreadsheet that only has 419 rows in it, and 13 columns. And the initial size of it, before I used only one "Filter" command, was 262kb; but after I used only one "Filter" command in it, it increased to a size of 37,837KB. So my question is, is it typical for an Excel spreadsheet to increase dramatically when we use these new functions, because I usually create spreadsheets (especially at work) to share with others and colleagues, but if these new functions are making these Excel spreadsheets so large that you can't do that, then wouldn't it make more sense to use the old methods of massaging the data instead of using these new functions?
Is there a way to do this with the sumifs function? Sumifs allows an array of filters, but I would love to apply this kind of method first. I suppose applying filter before sumifs would work but then I would need to filter the criteria volume too.
hmm i really need help.. i kinda confuse to figure out... i try make it simple as possible.. I have data require like 4 item item a 1 item b 2 item c 2 item d 1 and the amount that i have are item a 2 item b 4 item c 4 item d 2 the question.. how do i make the data become every amount that i have full fill each amount require gonna give amout of 1 in total.. in these data shows i should have the amount of 2.. how suppose i make in the excel? kinda confuse which formula i use.. and how i make the typing there.. any suggestion.. how i can done this?
This shows a list of 7 records that the filters include. How do I get the inverse result. I want a list of what the filter is excluding. Really I’m comparing 2 fact tables. I want a report of the records missing from 2nd fact table
It is funny. Comparing two lists/tables to extract items in both or items only in one is one of the most common questions I get over the last two decades. I have about 25 videos on this topic, but here is the FILTER example I think you want: ruclips.net/video/bRh45Yissbg/видео.html
Yes, XLOOKUP can do different types of lookup like approximate match lookup and last to first lookup when there are dups and other things much easier than FILTER. Each has their place. Lucky we have both!!!!
Hello sir, this technique quite good but my question is if you have multiple criteria, like in this case if you have twice the "Busan210" wheather this formula is delivered same result or it will delivered twice results.
This is probably a question about a Cross Join (Cartesian product)? Good question from relational approach. Answer - No. This is a one-way matching technique.
@@excelisfun sir if you give you email id or any other communication details then I can explain to you, what I want to say. I am trying power query with these latest function but I always stuck in some of the points, then trying to create new sheet---- so on. Which is become more lengthy process
I have this Lamda that does this: INLIST = LAMBDA(List1,List2 ,ISNUMBER( XMATCH(List1, List2) )); FILTERLIST = LAMBDA(Array,List1,List2 ,Filter(Array, INLIST(List1,List2) ));
@Joel Shafron BUT easier to simply convert the function in the video as is to a 2 parameter LAMBDA or simply add that function to Autocorrect so you can access it (with the same name as if it were named as a LAMBDA) and use it in any workbook without recreating.
@@richardhay645 How could this be done in 2 parameters? You need (What to filter and the 2 lists for comparisons). I've added this to the AFE so I don't need to recreated it, it is on any sheet I create.
Don't forget: ChatGPT often does not know what it is doing when you ask it to create a formula in the worksheet, M Code or DAX. I asked it: "Excel formula to extract records based on a list of items?" and it gave me:
To extract records in Excel based on a list of items, you can use the VLOOKUP function in combination with the IFERROR function. Here's an example formula:
=IFERROR(VLOOKUP(A2,$D$2:$E$6,2,FALSE),"Not Found")
In this formula:
A2 is the cell containing the item you want to search for
$D$2:$E$6 is the range of cells containing the list of items and their corresponding records
2 is the column number in the range that contains the records you want to extract
FALSE specifies an exact match
This formula will return the record for the item in cell A2 if it exists in the list, or "Not Found" if it doesn't.
You can drag this formula down to apply it to all the items in your list.
lol or : (
Then I asked it: "Excel formula to extract records from a table based on a list of criteria?" It gave me an AND Logical Test rather than the correct OR Logical Test, as shown here:
To extract records from a table in Excel based on a list of criteria, you can use the FILTER function. Here's an example formula:
=FILTER(Table1, (Table1[Column1]=A2)*(Table1[Column2]=B2))
In this formula:
Table1 is the name of your table
Column1 and Column2 are the names of the columns in your table that contain the criteria you want to filter by
A2 and B2 are the cells containing the criteria you want to filter by
This formula will return all the records in your table that meet both criteria specified in cells A2 and B2. You can modify this formula to include additional criteria by adding more conditions inside the parentheses, separated by an asterisk (*) for AND conditions or a plus sign (+) for OR conditions.
For example, if you want to filter by three criteria, you can use this formula:
=FILTER(Table1, (Table1[Column1]=A2)*(Table1[Column2]=B2)*(Table1[Column3]=C2))
This formula will return all the records in your table that meet all three criteria specified in cells A2, B2, and C2.
lol or : (
Then I asked it: "Excel formula to extract records from a table based on a list of criteria compared against one column in the table?" I got:
To extract records from a table in Excel based on a list of criteria compared against one column in the table, you can use the VLOOKUP function in combination with the FILTER function. Here's an example formula:
=FILTER(Table1,ISNUMBER(VLOOKUP(Table1[Column1],A2:A6,1,FALSE)))
In this formula:
Table1 is the name of your table
Column1 is the name of the column in your table that you want to compare against the list of criteria
A2:A6 is the range of cells containing the list of criteria you want to filter by
The VLOOKUP function looks up each value in Column1 against the list of criteria in A2:A6, and returns a match if found
The ISNUMBER function checks whether the VLOOKUP result is a number (i.e. a match was found)
The FILTER function returns all the records in your table that have a match in the list of criteria
This formula will return all the records in your table where the value in Column1 matches one of the criteria in the list. You can modify the formula to compare against a different column in your table by changing "Table1[Column1]" to the appropriate column name. You can also modify the formula to use a different range of criteria by changing "A2:A6" to the appropriate range of cells.
This is pretty close.
I wonder if it misinterpreted one of my videos and used VLOOKUP rather than XMATCH lol
Mike, you are the best. FILTER is my #1 goto function. It is an awesome function that does so much, and you make it do more (in a simple way) Job well done Mike.... =REPT("👍",1000000)
Thanks for the exceedingly awesome =REPT("👍",1000000)!!!!!!!
It never ceases to amaze me at how clearly you explain and guide through the logic of how to accomplish otherwise difficult concepts.
That is always my goal: tell a story with good details and good order to the details. The details and story should anticipate potential questions and answer them before they need to be asked : )
This is amazing Mike. FILTER is definitely one of my favorite and most-used functions. Thanks Mike!
You are welcome for the boomerang re-do of this trick you have seen before, Teammate Chris M lol
You know, I thought this looked a little familiar, but it was EXCEL-lent nonethless!
Mike, for so many of us your are not just a GOAT, you are THE GOAT. :-)
Thank you for all things Excel you do. :-)
Semper fi. 👍
Just a little goat who can tell stories and who is surrounded by smart goats like Geert and ExcelLambda and Mr Excel and so many more ; ) Go Team!!!!
What does "Semper fi" mean?
@@excelisfun ‘fi’ is an abbreviation for ‘fidus’ (as in ‘fidelity’), The expression means “forever loyal”.
It’s the slogan of one of the US armed forces, if I’m not mistaking.
The message I meant to convey was: we follow your YT-channel and always will. :-)
@@GeertDelmulle Thank you for the clever encryption and for the kind devotion, Teammate Geert!!!!! Go Team!
You are clearly the best teacher ever on excel ( at least on youtube) .. thank you , my excel guru and because of you people call me excel guru...
.. have been following you since 2009
I am so happy to be able to help you with Excel !!!!
Thank you Mike. Countif also works. =FILTER(Grades[[Student]:[Grade]],COUNTIF(Classes[Classes],Grades[Class]))
That's a great formula!!! I never remember to do that because in the old days MATCH functions was always faster than COUNTIFS, especially for array calculations. I have not timed the two since the new calc engine came out. I wonder...
@@excelisfungreat video Mike, hope you are well. Like you, I assumed countif would be slower. I don't think anything would have changed in that regard with the new calculation engine. I like xmatch. Although I'm surprised that they didn't include an option to put in a value for unmatched items when they released that function.
@@ricos1497 I did extensive timing for my 2012 book, and COUNTIFS was sometimes horribly slow - like blow your computer up slow. But I have not done extensive timing tests in the new calculation engine. I know that Excel MVPs Charles Williams and Mr Excel did some time and things were faster. I really should just go and create a big data set and time this... Interesting note: in my timing experiments the speed of calculations was always dependent on the combinations of functions and formula elements used - and this is because each function is programmed to work in a different way - so sometimes a certain combination would just not be efficient or effective - and others would work like a charm together.
@@excelisfun interesting. I always assumed that it was slow because COUNTIFS always referenced a range rather than an array, but I think that's the VBA programmer in me (passing a range of values to an array results in far quicker run times). I'd assumed that would still be the case, but probably not.
@@ricos1497 That could very well be the case. But interaction between underlying Microsoft coding affected things also.
that BAM! is worth so much Mike, thank you for reminding us this amazing trick!!!
You are "BAM" welcome!!!!!!
You know how people will argue about everything? Well in this case I would have approached the problem exactly the same way. Maybe after watching all your videos you have me trained!
lol too funny Go Team!!!!!!!
Great tutorial. FILTER is AWESOME!
I agree : ) : )
Great video again! Thanks for posting.
You are welcome for the posts, Roy!!!!!
I've been looking for a way to filter an array based on a list for a while now. I'm about to use the hell our of XMATCH going forward! Thanks!
You are welcome!
Excellent !! FILTER is the best
I agree: FILTER is the best!
I gave up QuickBooks for Excel over a year ago. That would not have been possible without DAs. All reports are based upon a Journal in which all transactions are recorded. My Journal and reporting models are built around FILTER,XLOOKUP,XMATCH,CHOOSECOLS,VSTACK,HSTACK and SUMIFFS. So far I have 6 instances of this exact construction in my report generators. My reporting model is still being fleshed out QuickBooks RIP!
RIP QB!!!!! I used to teach QuickBooks waaaaaay back about two decades ago... I love to hear that you are leveraging the DA to replace QB!!!!!
What's DA, please?
@Oluwaseun Ojelabi sorry, DA stands for Dynamic Arrays.
Great video!! ✌
For fun:
=FILTER(Grades[[Student]:[Grade]],IFNA(XMATCH(Grades[Class],Classes[Classes]),)) 😉
ExcelLambda!!!! : ) : ) Just for fun: I love that!!!!!!!
Mike you are my Super hero!
Glad to help : ) : )
Thanks Mike. Filter is an amazing function!!!
You already knew this trick, Formula Guy John!!! You are welcome as always : )
Mike, great insights!
It always amazes me others provide other solutions. We all know there's usually more than one way to do something in excel. Why do these people need to challenge you!
It's all in good fun. The more eyes the better. Plus, often I get to learn cool tricks I did not know. Go Team!!!!
Glad you like the insights, Keith!!!!!
Bam! Thank you Mike. Planning to use the FILTER function. 👍
a bigger BAM! please.
BAM!!!!!!
@@spilledgraphics Bigger BAM : ) : ) : )
Thank you Mike. You work is amazing, I've learned the FILTER function from you and it made a very complex task easy and fast. Thanks to you I am the Queen of Excel at work. Your videos are increadibelly informative. You are my hero.
Yes!!!!!! Queen Of Excel is Awesome : ) : ) : ) Your work is lucky to have you!
@@excelisfun Thank you, it means so much, especially coming from you! ❤️
@@nikoletahanakova8573 You tell them that I say the Queen of Excel needs a raise : ) : ) : ) : )
Thanks Mike for this EXCELlent video.
You are welcome, Fellow Teacher!!!!!
That sure beats putting a set of arrays added together in the 'include' argument. With that method, the list cannot be expanded or contracted without lots of editing. So, good show!
Yah, the Excel Table Column Object makes it conveniently dynamic : ) : )
@@excelisfun OMG. I once loved my women dynamic - now I'm in to formulas.
@@drsteele4749 lol too funny dynamic formulas...
Thanks Mike! Is Awesome!
Glad it is awesome for you, Excel H. Facil!!!!!!
Really cool trick Mike!
Glad it is cool for you!!!!
Thanks for posting yet another amazing video on Excel!
Since I started learning Power Query just three weeks ago, I am trying to use it everywhere possible. 😎
I was able to develop a solution delivering the same results as shown in the video using PQ to populate the filtered list of Student-Class-Grade (cols $I:$K) using a Students table INNER JOIN Classes table merge query.
I also used PQ to get a unique, sorted list ("Unqiue_Class") of the values in column D of the main table. I used the formula "=INDIRECT("Unique_Class")" to populate the data validation dropdowns for the cells in the Classes table in column G. Thank you kindly.
Yep! Perfectly!
In almost all data manipulations, I initially try to apply the "power" or logic of FILTER, literally "select" :)
And... we can do vertical FILTERing by column names in I4:K4, although not very convenient and "static" (TRUE/FALSE for all columns)
=FILTER(
FILTER(
Grades,
ISNUMBER( XMATCH( Grades[Class], Classes[Classes]))),
ISNUMBER( XMATCH( Grades[#Headers], I4:K4)))
which became more comfortable and "dynamic" (python-like list with the possibility of repetitions and backward indexing) with CHOOSECOLS. So, we can store column names in table/spilled array... kind of "meta" level :)
=CHOOSECOLS(
FILTER(
Grades,
ISNUMBER( XMATCH( Grades[Class], Classes[Classes]))),
XMATCH( I4:K4, Grades[#Headers]))
maybe should have used LET for readability :)
Yes, FILTER is a real gift from Microsoft : )
@@viktorasgolubevas2386 Great point! I have doen videos about filter by column with two FILTER, but that was before CHOOSECOLS came out. It seems that maybe CHOOSECOLS might be an easier to read formula?
Great!! Thanks Mike that is exactly what I was looking for
Glad this helps!!!
Awesome, I was needing this just yesterday! Now I know.
Yes!!! Now you know : )
This is AMAZING!
Glad it is amazing for you!!!!!!
Super useful...thanks Mike
Glad it is useful!!!!!
! Awesome video! Thanks for the great tip!
You are welcome!!!!
Thanks Mike! Always awesome!!
Glad it is awesome, Wayne !!!!!!
Clearly explanation, thanks.
You are welcome!
GREAT !!!
Thanks
You are welcome!!!
Amazing As Always
Glad you like the amazing !!!
Excellent trick
Glad you like it!!!!!
Super Trick ❤
Glad it is super for you!!!!
Awesome job. Really helpful.
Glad it helps, Scott!!!
Very cool Mr Mike ❤
Glad you like it, Vijay!!!
Oh, that's nice!
Excel Table part makes it real nice : ) : )
Thank you!
You are welcome, zt!!!!!!!
Thank U teacher.
You are welcome, Master Jack!!!!
Awesome!
Glad it is awesome!!!!!
Awesome video boss
Glad you like it, Sweet Chain Saw!!!!!
Great 👍
Glad it is great!!!!
Hi Mike, do you have a video on the "Switch" function? I'd like to learn how to use it from you
My MECS class has everything. Here is the lookup worksheet function MECS's video at the SWITCH function part:
ruclips.net/video/V0vvKFC1wc4/видео.html
Awesome, thanks Mike! I'll check it out.
@@qasimawan3569 : ) : ) : )
Wonderful
Glad you like it!!!
Thanks!
Thanks for all you do! I've learned a ton from your videos.
Thank you for the kind donation : ) : ) : ) It helps keep making vids and files!!!!
@@LAAdams17 I am glad that you can learn a lot : ) : ) : )
Mike, what Magic Trick will show me how to extract only columns 1 and 3? This is showing me columns 1 thru 3, which is excellent.
You can use the CHOOSECOLS function inside the array argument of FILTER, like: =FILTER(CHOOSECOLS(E3:G36,1,3),ISNUMBER(XMATCH(G3:G36,K3:K5)))
or double FILTER, like this:
=FILTER(FILTER(E3:G36,ISNUMBER(XMATCH(G3:G36,K3:K5))),{1,0,1})
Amazing trick
You seen this before, usman because you have been around a long time : ) But glad it is amazing this time too!!!!
Great vıdeo lıke as always. Thank a lot Mıke
You are welcome a lot!!!!!!
Nice trick Mike. I use reverse countif😂. Countif of criteria to range. We dont need to use isnumber since the result is either 0 or more than 0.
Very good tip!!! As I said below: I never remember to do that because in the old days MATCH functions was always faster than COUNTIFS, especially for array calculations. But now, maybe the new calc engine will provide a new perspective...
this is very helpful although I struggled to understand how did you make the red classes column, is it via "Data validation" ?
It is an Excel Table that can expand or contract based on data : ) Table with field name at top, then Ctrl + T.
I had the same question. It *IS* Data Validation. Mike has the list of values over to the right in column X.
Ooooo, I thought you were asking about the Table, not the cell with the data validation. Yes, as Cloin says, it is data validation. Here is a video if you would like: ruclips.net/video/fyPA9NK1eX0/видео.html
Could you add more column criteria?
Yes, then you could repeat process on each column and if an AND Logical Test multiply them and if it is an OR Logical Test you add.
It's precisely what I intended, but I don't have excel365, how to overtake, thanks😳
There is a way in older versions, but it is MUCH more complicted M 365: ruclips.net/video/TkaFVur8Nic/видео.html
This is perhaps the best example of how M 365 is so much better than any other version in history.
Thank you so much for your video inspiration,
But what to do when the formula we have is not working. Please just tell me
What is happening to your formula? What do you see when you try to create it?
Mike great video , can you make another video Xmatch,search , and filter in one example
What is the goal of the formula/ What is the formula trying to do?
@@excelisfun With reference to this example , if column D was serial numbers and column G is a list as it is .
Is it possible to combine search , xmatch and filter to produce the report you have made?
Your channel is really useful : GREATLY APPRECIATED ! Many years already :)
Have you got any trick to enclose the OR function in a match formula ? I'm looking to OR this match OR this match etc in the MATCH formula part... if the OR function only returns only TRUE or FALSE, which formula to use in order to get the same
Do you mean an explicit list of OR conditions? eg:
=FILTER(Grades[[Student]:[Grade]],
("Busn 210"=Grades[Class]) + ("Busn 216"=Grades[Class])
)
Or you can use countif function
As I mention a few other times in this thread: I never remember to do that because in the old days MATCH functions was always faster than COUNTIFS, especially for array calculations. But I wonder how they compare with the new calculation engine...
I had a problem where i was trying to extract data from a shared cloud based excel file using filter and tried a few other functions. They just returned nothing, not found an explanation why they don't work. If i looked at the file locally it all worked as expected.
I am not sure either. Anyone else know?
This may sound like a dumb question, but have you tested to find out if the cloud file has to be open for the formula to work? Many functions don't update unless the target file is open - even if it is shared in the cloud.
Also, are you sure the reference to the shared cloud file is correct? I've seen weird cloud setups that require a tweak to formula path or PQ code.
@@msmith3090 Not at all. It was a while back so can't 100% remember but i believe it would of been as i would of been working on both files at the same time. I was aware about a target file having to be open as have another one that's shared as a live stock list that's referenced to another master workbook which only updates if its open.
If i direct reference to the cells i want to pull from the master workbook it works.
Using CHOOSECOLS or FILTER just returns blank. I think i tried another function as well but i cant find what i tried looking back.
If the file is local say in the same directory the lookups work as expected. It breaks when its in the cloud unless using direct cell referencing. When I'm deep in excel again I'll revisit it as I'm still learning new better ways to do things as i go along.
Hi, I need your help. I want to calculate cash amounts between two dates for individual clients from a drop down list, so if i choose a client from the drop down list and then choose the date range it will just show that figure for that person only. I know how to sum between dates but not using a drop down list to do it.
Mike, what will happen to excel after chatgpt?
Nothing. So far CHATGPT can not write correct Excel worksheet, M Code or DAX formulas. Try it. Some of the formulas it suggests are not only wrong, but if they calculate correctly, they are terribly inefficient. Remember: that chat thing searchs the internet and much of the Excel and Power BI calculation stuff is junk. Especially Microsoft Help... However, it can zero in on close stuff if you ask the right question. But often, the person asking the question is not equipped to decipher if the result is accurate or efficinet... Look at the post I pinned to the top of this comment section.
Here's my issue with the filter function. It makes the excel spreadsheet so large that I can't email or share it. You can't even upload it to a Google Drive to share because it's so large, not to mention that it takes a much longer time to open up. I have a spreadsheet that only has 419 rows in it, and 13 columns. And the initial size of it, before I used only one "Filter" command, was 262kb; but after I used only one "Filter" command in it, it increased to a size of 37,837KB. So my question is, is it typical for an Excel spreadsheet to increase dramatically when we use these new functions, because I usually create spreadsheets (especially at work) to share with others and colleagues, but if these new functions are making these Excel spreadsheets so large that you can't do that, then wouldn't it make more sense to use the old methods of massaging the data instead of using these new functions?
Can this work in different sheets?
When I enter, 34000 my last monthly payment value didn't change -413.15 showing not -600.57
Is there a way to do this with the sumifs function? Sumifs allows an array of filters, but I would love to apply this kind of method first. I suppose applying filter before sumifs would work but then I would need to filter the criteria volume too.
Applying filters before SUMIFS is the way to go.
hmm i really need help.. i kinda confuse to figure out...
i try make it simple as possible..
I have data require like 4 item
item a 1
item b 2
item c 2
item d 1
and the amount that i have are
item a 2
item b 4
item c 4
item d 2
the question.. how do i make the data become every amount that i have full fill each amount require gonna give amout of 1 in total.. in these data shows i should have the amount of 2.. how suppose i make in the excel? kinda confuse which formula i use.. and how i make the typing there.. any suggestion.. how i can done this?
This shows a list of 7 records that the filters include. How do I get the inverse result. I want a list of what the filter is excluding. Really I’m comparing 2 fact tables. I want a report of the records missing from 2nd fact table
It is funny. Comparing two lists/tables to extract items in both or items only in one is one of the most common questions I get over the last two decades. I have about 25 videos on this topic, but here is the FILTER example I think you want:
ruclips.net/video/bRh45Yissbg/видео.html
okay how do i extract things that are not in the list using the list? Kinda like 'NOT'
Here is one way: ruclips.net/video/3b1gbOJa08k/видео.html
Is there anything that XLOOKUP can do, that FILTER can't do?
I've gotten to where I use FILTER, even when I know I just need to return a single value.
Yes, XLOOKUP can do different types of lookup like approximate match lookup and last to first lookup when there are dups and other things much easier than FILTER. Each has their place. Lucky we have both!!!!
@@excelisfun Good points! Thanks for the reply.
@@Quidisi : )
Hello sir, this technique quite good but my question is if you have multiple criteria, like in this case if you have twice the "Busan210" wheather this formula is delivered same result or it will delivered twice results.
I am sorry, I do not understand your question.
This is probably a question about a Cross Join (Cartesian product)?
Good question from relational approach.
Answer - No.
This is a one-way matching technique.
@@excelisfun sir if you give you email id or any other communication details then I can explain to you, what I want to say.
I am trying power query with these latest function but I always stuck in some of the points, then trying to create new sheet---- so on. Which is become more lengthy process
@@viktorasgolubevas2386 sir this question is related to multiple matches with multiple results
Same results. Try using the exercise file Mike has shared. Try try try
Nice, I use NOT(ISNA(. They should make an ISXMATCH( to simplify our lives 😁
I have this Lamda that does this:
INLIST = LAMBDA(List1,List2
,ISNUMBER(
XMATCH(List1, List2)
));
FILTERLIST = LAMBDA(Array,List1,List2
,Filter(Array,
INLIST(List1,List2)
));
Nice : ) : ) : )
@@JoelShafron LAMBDA to the rescue!!!!
@Joel Shafron BUT easier to simply convert the function in the video as is to a 2 parameter LAMBDA or simply add that function to Autocorrect so you can access it (with the same name as if it were named as a LAMBDA) and use it in any workbook without recreating.
@@richardhay645 How could this be done in 2 parameters? You need (What to filter and the 2 lists for comparisons).
I've added this to the AFE so I don't need to recreated it, it is on any sheet I create.