Excel XLOOKUP Function Examples - 8 Useful XLOOKUP Examples
Vložit
- čas přidán 2. 08. 2024
- In this video, we look at 8 Excel XLOOKUP function examples to show off the power and versatility of this brilliant Excel function.
Learn ALL the best Excel functions - amzn.to/3Rg87Go
You can download the file to follow along here - www.computergaga.com/_excel/f...
Here are the chapters of the video contents;
00:00 Introduction
00:34 Classic XLOOKUP and dynamic array behaviour
03:12 Range lookup with XLOOKUP
05:32 Rank the top 5 products by sales value
07:52 Return the last match in a list
09:27 Two way lookup with two XLOOKUPs
12:23 Multiple criteria lookup
14:08 Interactive chart
17:02 XLOOKUP to help a complex COUNTIF
We start with a typical XLOOKUP example to look up some data and then progress through a variety of scenarios where XLOOKUP helps achieve our goal easily.
These include ranking values, creating dynamic charts, analysing with COUNTIF and performing a lookup with multiple criteria.
This function is a great VLOOKUP alternative. But also a great alternative to HLOOKUP and the INDEX/MATCH combination.
By the end of these Excel XLOOKUP function examples, you will know why we are all excited about this Excel function.
Find more great free tutorials at;
www.computergaga.com
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/UltimateExcel
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1 - Jak na to + styl
I must say its best video on Xlookup on YT.. Thanks Buddy
Wow, thanks!
Great video. I learned recently how to add a nice touch to interactive graph titles so that they retain formatting of the value shown. In the case of the sales total, the formula can be modified as follows: =B9&" total - "&TEXT(C9,"$#,###")
This will allow the figure to be shown in currency format. Cheers
Hi Alan.. awesome! Thanks for the many examples. XLOOKUP can really stand in for a lot of the old functions.. and make life so much simpler. Love the nested XLOOKUP/XLOOKUP for two-way lookup and the concatenated lookup_value and lookup_array examples.. brilliant! Thanks for sharing your tips and tricks. Thumbs up for Computergaga!!
Thank you Wayne.
Amazing the universe of options now opened with XLOOKUP. And wonderful that we have somebody like you sowing it to us. Thank you very much.
My pleasure Angelo. Thank you.
Instructive as always!! One of the best excel channels on YT!
Thank you. Much appreciated 😀
XLOOKUP has opened a new sea of possibilities and has also many short comings of previous look up functions in previous Excel versions. Thanks for this brilliant video Alan :)
Absolutely. Thank you Sachin.
Example 5 is exactly what I was looking for - thank you! Cheers mate!
My pleasure, Jared. Great to hear.
Thank you so much for this video! Awesome and so helpful.
You're welcome, Petra. Thank you.
Awesome Tutorial Really Helpful!XLOOKUP Certainly A Cool Function!Thank You Alan :):):)
My pleasure Darryl. Yes, it is fantastic.
That's very rich and awesome, thanks Alan .
My pleasure. Thank you.
Very good video and examples. Well Done!!
Thank you, Dale.
Goood...Thank you Alan!
Thank you Luciano.
really great Tutorial.
Thank you, Ishan.
great Alan
Thank you 😊
top quality channel this, thanks very much
Thank you very much Jonny.
Thanks for example 👍👍
You're welcome Muhammad.
Thanks, great video! With the ranking of the top 5 products, what if two products have the same value? How can get it to return each result not just the first result?
awesome tutorial
Thank you Kalyan.
very good !
Thank you.
Awesome. Number 6 is exactly what i needed. I had a work around in place but had to use a helper column to concatenate my data. This does exactly what need. thanks!
It is great isn't it. Thank you Von.
In the video example #3 Ranking the top five items by sales xlookup works great if all the sales totals are different. If one or more are the same xlookup does not work. I have thought about and tried different approaches but I have not found a solution. I am hoping that there is an easy solution because I think xlookup is great function. Thank you.
Yes, ranking values is always interesting. I have a video solving your question here Dale - czcams.com/video/sk3z8e2mvng/video.html
But I could make 20 of these. Because there is always the question of, if there are tied values, how should we rank them. What decides who is higher in the list, or how to rank them equally.
Hi Alan, for example 3 Ranking, how do I tweak the formula to accommodate products with exact sales number?
E.g. If Carrot Cake and Chocolate Brownie are having sales of 126, how do I tweak the formula so both results can be shown? Thanks!
This video will show a way my friend - czcams.com/video/sk3z8e2mvng/video.html
Hi can you help with counting entries between time but same date, example: wanted to count how many cars came in between midnight 00:00 am to 06:00 am morning. I'm looking for a simple formula
You can use the COUNTIFS function. Depending on your spreadsheet you could use a formula such as =COUNTIFS(A2:A11,">="&D2,A2:A11,"
@@Computergaga thank you for your reply. Unfortunately I have tried that too but no luck. Also wanted to let you know that I'm using formula to register the time automatically when a car enter i only type the registration number and time comes. So if you know any other formula that would work with formula then it help.
Thank you for the video. Download link is dead.
You're welcome, Ezel. Download link is fixed.
Can you help me I watch you videos but I am stuck. Can you show how to move rows to another sheet if a criteria is met. What I am trying to do is create a sheet where outstanding balances are greater than zero. I am using excel 2011
You could use a PivotTable with a filter set for >0.
Hi,
i am using Office 365. in my PC Xlookup function is not working. can you please help? Thanks
You don't have it? Try running an update, or switch to the Monthly channel. Instructions on how to do that here - bit.ly/2G8qHhx
@@Computergaga thanks a lot. unfortunately i don't have admister rights to do it. but at least now i know how to do it.
You are an excel Rockstar: Alan, not only can you spin yarn and expand the universe but also are a culinary connoisseur (cheesecake is far superior to apple strudel). Also two xlookups you've gone too far. Thanks for all you do.
😂 My pleasure. Thank you Rob.