Love is the most important thing in the world. So the more love we have the better the world will be. Thanks for the more love and I send my love to you too!!!!!!!!!!
Never would’ve thought to use XLOOKUP in this way… cool tip. Another function suitable for this example is the FILTER function, as it’s capable of filtering either rows or columns: =FILTER(B5:E16, B4:E4=H2) Cheers!
The old new-school way (before CHOOSECOLS!!) to do this with FILTER was: FILTER(A2:E40,{0,1,0,0,0})to return the complete second column, for example. CHOOSE COLS not only returns complete columns but also permits colums to be returned in any order. Before CHOOSECALS the reordering of complete columns had to be accomplished with each column to be returned being separately loaded into CHOOSE in the order to be returned. This was a real pain to return multiple columns in the desired order. CHOOSECOLS needs to be enhanced by Excel to accept cell references to the header names so that the hard-coded column index numbers do not have to be manually.adjusted if you add or delete columns.
@@richardhay645 In this example, the goal is to produce dynamic results based on the item selected from the data validation list in cell H2. Entering a static array of 0’s and 1’s to filter by column will not get the job done. To make it dynamic, just write some filter criteria for the header row, like this: =FILTER(B5:E16, B4:E4=H2) To your point about returning multiple columns in a different order than the source data, there are various ways of accomplishing this dynamically without the use of CHOOSECOLS or CHOOSE. The FILTER function is perfectly capable, when nested inside the SORTBY function, like this: =SORTBY(FILTER(B5:E16, COUNTIF(H2:I2,B4:E4)), XMATCH(H2:I2,B4:E4)) Alternatively, the INDEX function is also capable of returning multiple columns in any order, but requires the use of the SEQUENCE and ROWS functions to return every row, like this: =INDEX(B5:E16, SEQUENCE(ROWS(B5:E16)), XMATCH(H2:I2,B4:E4) Isn’t Excel fun? ;-)
915k and counting! I really love the the choosecol formula! Now so will everyone else! Hey Mike, you think you could do some complicated lambda helper function videos? I've put together some beauties in the last few weeks that I'd love to share!
CHOOSECOLS is pretty awesome, especially when you need to pull different columns into a single table. but XLOOKUP is the way to go when it is just one column, as we will see next video. As for you LAMBDA formulas and making videos, I can't do it for a long while, as i am busy taking care of my Mom in CA and I an writing two new books for Mr Excel and I am months behind... But when I can get a break....
Thanks Mike for this great Video. I faced the same issue when using the let function with Sumifs. I got an error because SUMIFS couldn't handle arrays. I had to use the MMult function to do a sumifs
Ooo : ) Big Smiles, Nader: because the next two videos will answer both questions: What to use in SUMIFS rather than CHOOSECOLS and what functions to use as a substitute for MMULT : )
I will not have one out anytime soon. I don't know python, yet ; ) Currently I am writing two books: one about M Code and one about Dynamic Array Formulas. Plus, I am taking care of my elderly Mom. I have no time at all... : ( : ( I can't wait, but it will be a while.
Alternatively, you can select the table, press CTRL+SHIFT+F3, select the top row as names and reference G5 to H2 (using the indirect function), can’t you?
I will not have one out anytime soon. I don't know python, yet ; ) Currently I am writing two books: one about M Code and one about Dynamic Array Formulas. Plus, I am taking care of my elderly Mom. I have no time at all... : ( : ( I can't wait, but it will be a while.
@@excelisfunoh I wish you get time for teaching us SQL as well, I have learnt so much from your book, you made understanding DAX so easy and when people say they don’t understand it’s filter function and then I realise how good teacher you are.
Re: CHOOSECOLS and XMATCH I just want certain columns like this using ColName named range. =CHOOSECOLS( FILTER(Table1, Table1[Row Name]="Mike Girvin"), XMATCH(ColName!C3:C11,ColName!C14,ColName!C17)) got #VALUE! error. HELP! Thanks.
Hey Mike, several videos ago someone commented "I love you". I just want you to know, I love you more. Thanks again for the content you share with us.
Love is the most important thing in the world. So the more love we have the better the world will be. Thanks for the more love and I send my love to you too!!!!!!!!!!
Wow!!!!never stop learning from you Mike
We just keep having lots of fun!!!
I like that you show the old school way because some companies aren't using 365. Thank you!
Never would’ve thought to use XLOOKUP in this way… cool tip. Another function suitable for this example is the FILTER function, as it’s capable of filtering either rows or columns:
=FILTER(B5:E16, B4:E4=H2)
Cheers!
The old new-school way (before CHOOSECOLS!!) to do this with FILTER was:
FILTER(A2:E40,{0,1,0,0,0})to return the complete second column, for example. CHOOSE COLS not only returns complete columns but also permits colums to be returned in any order. Before CHOOSECALS the reordering of complete columns had to be accomplished with each column to be returned being separately loaded into CHOOSE in the order to be returned. This was a real pain to return multiple columns in the desired order. CHOOSECOLS needs to be enhanced by Excel to accept cell references to the header names so that the hard-coded column index numbers do not have to be manually.adjusted if you add or delete columns.
Yah, I left an example in the download workbook, but did not show it in the video because XLOOKUP is just so easy : ) : )
@@richardhay645 In this example, the goal is to produce dynamic results based on the item selected from the data validation list in cell H2. Entering a static array of 0’s and 1’s to filter by column will not get the job done. To make it dynamic, just write some filter criteria for the header row, like this:
=FILTER(B5:E16, B4:E4=H2)
To your point about returning multiple columns in a different order than the source data, there are various ways of accomplishing this dynamically without the use of CHOOSECOLS or CHOOSE. The FILTER function is perfectly capable, when nested inside the SORTBY function, like this:
=SORTBY(FILTER(B5:E16, COUNTIF(H2:I2,B4:E4)), XMATCH(H2:I2,B4:E4))
Alternatively, the INDEX function is also capable of returning multiple columns in any order, but requires the use of the SEQUENCE and ROWS functions to return every row, like this:
=INDEX(B5:E16, SEQUENCE(ROWS(B5:E16)), XMATCH(H2:I2,B4:E4)
Isn’t Excel fun? ;-)
That is so awesome. Thank Mike for this EXCELlent video.
You are welcome for the video, Most Awesome Fellow Teacher Syed Muzammil Mahasan Shahi : ) : ) : )
915k and counting! I really love the the choosecol formula! Now so will everyone else!
Hey Mike, you think you could do some complicated lambda helper function videos? I've put together some beauties in the last few weeks that I'd love to share!
CHOOSECOLS is pretty awesome, especially when you need to pull different columns into a single table. but XLOOKUP is the way to go when it is just one column, as we will see next video. As for you LAMBDA formulas and making videos, I can't do it for a long while, as i am busy taking care of my Mom in CA and I an writing two new books for Mr Excel and I am months behind... But when I can get a break....
That's Amazing Mike ...the comparison is great ... i wish you extend the explanation to include criteria for rows too.
OK, I'll have to make a video for you : )
Your video that i made for you comes out today in about 8 hours : )
Thanks Mike. :) awaiting the next!!!
You are welcome, Formula Guy!!! Next video is not out until Sunday at 6 Am, though... : (
@@excelisfun N.p I will here on Sunday surely.
@@johnborg5419: ) : )
Good tips Mike! Thanks!
You are welcome, Luciano!!! : ) : )
Thanks Mike. Always informative
You are welcome, Matt!!!! : ) : )
Thanks Mike for this great Video. I faced the same issue when using the let function with Sumifs. I got an error because SUMIFS couldn't handle arrays. I had to use the MMult function to do a sumifs
Ooo : ) Big Smiles, Nader: because the next two videos will answer both questions: What to use in SUMIFS rather than CHOOSECOLS and what functions to use as a substitute for MMULT : )
Next video is out on Sunday though...
@@excelisfun perfect looking forward to them :) 😀
@@nadermounir8228 : ) : )
Hi Mike, Eagerly awaiting your first video of python in Excel. When we can epwct the same ? Regards amit
I will not have one out anytime soon. I don't know python, yet ; ) Currently I am writing two books: one about M Code and one about Dynamic Array Formulas. Plus, I am taking care of my elderly Mom. I have no time at all... : ( : ( I can't wait, but it will be a while.
@@excelisfun ok Mike. Take your time and take care of your mom. Say hi to your mom from my side. Regards, amit
@@TaxMentors Thank you : )
Python in excel now. Wow can’t wait for a lesson!!
I don't know python, so I can't make a video... But Mr Excel and Mynda both released videos today!!!
Here is Mr Excel's video: ua-cam.com/video/KIhDQDtvZPg/v-deo.html
Here is Mynda's video: ua-cam.com/video/-_1IaUjO-hk/v-deo.html
PANDAS and a Python being added to the Excel zoo!! Quite exciting!!! (BTW Leila's =PY() video this morning quite good also!)
Here is Lelia's video also: ua-cam.com/video/FbBXtqtRnWU/v-deo.html
thanks
Hope your Mom is doing well...
Alternatively, you can select the table, press CTRL+SHIFT+F3, select the top row as names and reference G5 to H2 (using the indirect function), can’t you?
😄 👍👌...
Hi sir, any planning to start python with excel series
I will not have one out anytime soon. I don't know python, yet ; ) Currently I am writing two books: one about M Code and one about Dynamic Array Formulas. Plus, I am taking care of my elderly Mom. I have no time at all... : ( : ( I can't wait, but it will be a while.
@@excelisfunoh I wish you get time for teaching us SQL as well, I have learnt so much from your book, you made understanding DAX so easy and when people say they don’t understand it’s filter function and then I realise how good teacher you are.
@@mishalqamar7302 I am sorry I can't teach SQL and Python right now... : (
Re: CHOOSECOLS and XMATCH
I just want certain columns like this using ColName named range.
=CHOOSECOLS( FILTER(Table1, Table1[Row Name]="Mike Girvin"), XMATCH(ColName!C3:C11,ColName!C14,ColName!C17))
got #VALUE! error. HELP! Thanks.