Lookup Column with CHOOSECOLS, INDEX or XLOOKUP function? Excel Magic Trick 1835

Download Excel File: excelisfun.net/files/EMT1835-...
Learn about how to lookup a column using CHOOSECOLS, INDEX or XLOOKUP functions.
Topics:
1. (00:00) Introduction
2. (00:10) CHOOSECOLS and XMATCH
3. (01:10) XLOOKUP
4. (01:43) INDEX and XMATCH
5. (03:20) Closing, Video Links
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #powerbi #powerquery #powerbidesktop #freeclass #freecourse #freeclasses #excelclasses #powerquery #powerquerytutorial #microsoftexcel #microsoftmvp #lookup #xlookup #vlookup #vlookupformula

[@wizardofaus8473]

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.

[@excelisfun]

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!!!!!!!!!!

[@lucaviglio1206]

Wow!!!!never stop learning from you Mike

[@excelisfun]

We just keep having lots of fun!!!

[@andrewjohnson4352]

I like that you show the old school way because some companies aren't using 365. Thank you!

[@SyedMuzammilMahasanShahi]

That is so awesome. Thank Mike for this EXCELlent video.

[@excelisfun]

You are welcome for the video, Most Awesome Fellow Teacher Syed Muzammil Mahasan Shahi : ) : ) : )

[@Luciano_mp]

Good tips Mike! Thanks!

[@excelisfun]

You are welcome, Luciano!!! : ) : )

[@TopBam]

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!

[@excelisfun]

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....

[@johnborg5419]

Thanks Mike. :) awaiting the next!!!

[@excelisfun]

You are welcome, Formula Guy!!! Next video is not out until Sunday at 6 Am, though... : (

[@johnborg5419]

@@excelisfun N.p I will here on Sunday surely.

[@excelisfun]

@@johnborg5419: ) : )

[@davidabuang]

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!

[@richardhay645]

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.

[@excelisfun]

Yah, I left an example in the download workbook, but did not show it in the video because XLOOKUP is just so easy : ) : )

[@davidabuang]

@@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? ;-)

[@fernando5166]

thanks

[@HusseinKorish]

That's Amazing Mike ...the comparison is great ... i wish you extend the explanation to include criteria for rows too.

[@excelisfun]

OK, I'll have to make a video for you : )

[@excelisfun]

Your video that i made for you comes out today in about 8 hours : )

[@mattschoular8844]

Thanks Mike. Always informative

[@excelisfun]

You are welcome, Matt!!!! : ) : )

[@nadermounir8228]

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

[@excelisfun]

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 : )

[@excelisfun]

Next video is out on Sunday though...

[@nadermounir8228]

@@excelisfun perfect looking forward to them :) 😀

[@excelisfun]

@@nadermounir8228 : ) : )

[@cato451]

Python in excel now. Wow can’t wait for a lesson!!

[@excelisfun]

I don't know python, so I can't make a video... But Mr Excel and Mynda both released videos today!!!

[@excelisfun]

Here is Mr Excel's video: kzbin.info/www/bejne/gXrLdYR6qduNhsk

[@excelisfun]

Here is Mynda's video: kzbin.info/www/bejne/Y5CUepSLn7Rgns0

[@richardhay645]

PANDAS and a Python being added to the Excel zoo!! Quite exciting!!! (BTW Leila's =PY() video this morning quite good also!)

[@excelisfun]

Here is Lelia's video also: kzbin.info/www/bejne/fJOliaenqbehjbc

[@jitulvslife03]

Hope your Mom is doing well...

[@MerkDolf]

😄 👍👌...

[@TaxMentors]

Hi Mike, Eagerly awaiting your first video of python in Excel. When we can epwct the same ? Regards amit

[@excelisfun]

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.

[@TaxMentors]

@@excelisfun ok Mike. Take your time and take care of your mom. Say hi to your mom from my side. Regards, amit

[@excelisfun]

@@TaxMentors Thank you : )

[@josecarlosconejo5724]

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?

[@shadow_gaming_sk]

Hi sir, any planning to start python with excel series

[@excelisfun]

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.

[@mishalqamar7302]

@@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.

[@excelisfun]

@@mishalqamar7302 I am sorry I can't teach SQL and Python right now... : (

[@txreal2]

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.