Another great video! I did not realize I could use xlookup to define the return array. Thank you for the challenge. This helps me find practical uses for this formula in my daily work.
Absolutely commendable teaching work. These are cases that any user, sooner or later, will have to learn to solve. As always, excellent teaching and mastery. Thanks Jon.
Fantastic video. I love using xlookup but didn't realize it could grab the entire range in a table (thought it had to be 1 row or 1 column). No more index/match for me! Thanks, Jon!
What disheartens me the 'most' about this, is that there's no real way in hell, I'd ever 'come up' with this idea by myself. There's no way I'd be able to 'backwards engineer' this solution MYSELF.... unless.... I was an expert like yourself! Catch 22. Your explanation is great. I can 'follow' it.
Great video and demostrates multiple features like benefit of tables manage formula consistency and future proof models. One thing that I am.interested in is how you calculate the time to process the formula? My team design and manage incredibly complex financial models so gains like 17% in workbook performance could be invauable. Cheers J
Great idea on using data validation to control the user inputs! For anyone else reading this, here is a video on how to create drop down lists, which can help prevent data entry errors. th-cam.com/video/KGnvCKiOLM0/w-d-xo.html
Great trick specially on the approximate match. Instead of hiding the columns, I would have used a literal constant like this {0,20000,50000,100000} on the second lookup array.
Thanks for sharing this great videos. Can you also explain how i can i do an xlookup when there are multiple lines with the same data and where only a column with a date and time is different and you need to find the data with the most recent date? The date and time are not known. The lookup value is not unique the date and time makes it unique
Great question! Yes, as long as the cell contains a date, XLOOKUP will find the match regardless of the date formatting that is applied. The date contains an underlying number value. XLOOKUP looks at that value instead of the date formatting that is applied. The caveat is if the cell contains text instead of a date data type. Here is a video that explains more about the calendar system in Excel. I hope that helps. 🙂
I’m not an Excel person, so I need your help here. In the closest match section, you have the commission ranges being 0-20,000 is 2% commission, and 20,000-50,000 is 4% commission etc. If the person got 20,000 in sales, what is the commission, 2% or 4%? For 4% shouldn’t the range start at 20,001? Or rewrite the 2% range 0-19,999 so the minimum dollar amount for 4% would be 20,000.
Great question! And sorry I didn't explain that in this video. I do have another video comparing VLOOKUP(CHOOSE()) with INDEX MATCH that explains the time calculation in more detail. th-cam.com/video/Tkn1VlFc-QQ/w-d-xo.html I use a simple macro that sets a timer and switches from Manual to Automatic calculation to calculate the formulas and record the duration. You can download the file that contains the macro on the accompanying blog post for that video. www.excelcampus.com/vba/vlookup-choose-vs-index-match/ There are other, more robust ways to monitor calculation time, but that method gets the job done for this scenario. I hope that helps. Thanks again and have a nice day! 🙂
Brilliant ingenuity. The key to ingenuity is knowledge of how the tools work. I pose a theory, then set about to prove it can work. I believe the designers of these functions and formula didn't know all the ways they can be used. It's fascinating!! My brain plasticity benefits when i solve challenges with excel. Maybe, Microsoft or The National Institutes of Health should study the effects of Excel puzzles on brain dexterity. Will too much AI retard brain power? I think yes.
Another great video! I did not realize I could use xlookup to define the return array. Thank you for the challenge. This helps me find practical uses for this formula in my daily work.
Absolutely commendable teaching work. These are cases that any user, sooner or later, will have to learn to solve. As always, excellent teaching and mastery. Thanks Jon.
Thanks so much, Ivan! I really appreciate your kind words and continued support. 🙏
Fantastic video. I love using xlookup but didn't realize it could grab the entire range in a table (thought it had to be 1 row or 1 column). No more index/match for me! Thanks, Jon!
Awesome! I'm happy to hear you learned something new. Thanks! 🙌
Going to try the challenge this weekend. thanks
Excellent video Jon. The exercises are always great.
Thanks so much, Orel! 🙂
Excellent. Something different i learnt. Sincerely Thank You
Awesome! I'm happy to hear it. 🙂
What disheartens me the 'most' about this, is that there's no real way in hell, I'd ever 'come up' with this idea by myself. There's no way I'd be able to 'backwards engineer' this solution MYSELF.... unless.... I was an expert like yourself! Catch 22. Your explanation is great. I can 'follow' it.
Marvelous, value added knowledge
Thanks Yussalfian! I'm happy to hear it! 😊
Nice video, thanks Jon!
Thanks Chris! I appreciate your support!
Great video and demostrates multiple features like benefit of tables manage formula consistency and future proof models. One thing that I am.interested in is how you calculate the time to process the formula? My team design and manage incredibly complex financial models so gains like 17% in workbook performance could be invauable. Cheers J
Great tutorial 👌
Thanks Alex! 🙂
Thanks Jon for a wonderful video on Xlookup along with different scenarios.
Thanks Maliha! 🙌
Great stuff obviously another way to do the air handling would be to make the team names and plan names a list that has to match but point taken
Great idea on using data validation to control the user inputs! For anyone else reading this, here is a video on how to create drop down lists, which can help prevent data entry errors.
th-cam.com/video/KGnvCKiOLM0/w-d-xo.html
Thank you, Jon!
Man you're amazing.
Thanks for sharing.
very good
Great trick specially on the approximate match. Instead of hiding the columns, I would have used a literal constant like this {0,20000,50000,100000} on the second lookup array.
thnx :)
Thanks for sharing this great videos. Can you also explain how i can i do an xlookup when there are multiple lines with the same data and where only a column with a date and time is different and you need to find the data with the most recent date? The date and time are not known. The lookup value is not unique the date and time makes it unique
Can we do two way lookup with dynamic (#) which work on a large data set?
Regarding the first tip, does it work the same if the cells have the dates in different local date format? Example: if the cell is YY-MM-DD ?
Great question! Yes, as long as the cell contains a date, XLOOKUP will find the match regardless of the date formatting that is applied.
The date contains an underlying number value. XLOOKUP looks at that value instead of the date formatting that is applied.
The caveat is if the cell contains text instead of a date data type.
Here is a video that explains more about the calendar system in Excel.
I hope that helps. 🙂
@ExcelCampus I noticed that I asked this question for another video of you about short cuts for dates :)) thanks for the reply anyways! Will try it
I’m not an Excel person, so I need your help here.
In the closest match section, you have the commission ranges being 0-20,000 is 2% commission, and 20,000-50,000 is 4% commission etc.
If the person got 20,000 in sales, what is the commission, 2% or 4%?
For 4% shouldn’t the range start at 20,001?
Or rewrite the 2% range 0-19,999 so the minimum dollar amount for 4% would be 20,000.
Ummm, how do you do a formula for how much time it takes to calculate a formula??
Great question! And sorry I didn't explain that in this video. I do have another video comparing VLOOKUP(CHOOSE()) with INDEX MATCH that explains the time calculation in more detail.
th-cam.com/video/Tkn1VlFc-QQ/w-d-xo.html
I use a simple macro that sets a timer and switches from Manual to Automatic calculation to calculate the formulas and record the duration.
You can download the file that contains the macro on the accompanying blog post for that video.
www.excelcampus.com/vba/vlookup-choose-vs-index-match/
There are other, more robust ways to monitor calculation time, but that method gets the job done for this scenario.
I hope that helps. Thanks again and have a nice day! 🙂
Brilliant ingenuity. The key to ingenuity is knowledge of how the tools work. I pose a theory, then set about to prove it can work.
I believe the designers of these functions and formula didn't know all the ways they can be used. It's fascinating!!
My brain plasticity benefits when i solve challenges with excel.
Maybe, Microsoft or The National Institutes of Health should study the effects of Excel puzzles on brain dexterity.
Will too much AI retard brain power? I think yes.
Great tutorial. Dumb system. Give me a real DB every time.
But a real data base is for the super expanded user which it appears you are. For the nominal non data base user, excel is simply easy and awesome.
@@mhebert3842 It's the easy part I quibble with. But we'd have to sit down over coffee . . . You do you.