Download Excel File (to follow along with video): excelisfun.net... Learn about the new Office 365 XLOOKUP Function and what to do if you have three lookup values.
Пікірлер: 109
@excelisfun5 жыл бұрын
02:39 Bonus formula : )
@sasavienne5 жыл бұрын
Xlookup is nothing without teachers like Mike who explain how we can make the best use of it. Great job. Thank you...
@excelisfun5 жыл бұрын
You are welcome for the teaching, Salim : ) : ) : ) : )
@nigelbalcarres5857 ай бұрын
Superb formula. I have been following you for years, and anyone who joins my team gets sent straight to your tutorials
@excelisfun7 ай бұрын
I am happy to help your Team : ) : ) Go Team!!!!!
@aspiringcoder20247 ай бұрын
This is the most helpful, concise tutorial on XLOOKUP that I could find on KZbin. Thank you for posting this!
@excelisfun7 ай бұрын
You are welcome!!!!
@OakleyTurvey5 жыл бұрын
The fun never stops!
@excelisfun5 жыл бұрын
NEVER!!!! : )
@MJKhan-t9j5 жыл бұрын
Will learn this Xlookup as soon as possible, my dad is excited for many years he is using vlookup , he is watching this video now
@excelisfun5 жыл бұрын
Thanks for your comment, Live wire!!! I love your videos. They are so fun and happy and smart! You have very fun and happy and smart, young man! Tell you Dad hello from me.
@Hey_Delight5 жыл бұрын
Thanks Sir for the free lecture. More power. Permission to post below... Dear Microsoft Developers, If you updated the amazing Vlookup and Hlookup to a fabulous Xlookup, let it be available in all MS Excels, not just in 365. Thank you so much, guys...for all your brilliant ideas. The message remains the same, You guys are phenomenal.
@excelisfun5 жыл бұрын
Dear Delight in Life, You are welcome for the free lecture : ) I have delivered your message many times to Microsoft. But they 100% said that they will ONLY have new features in Office 365. It is too bad, because all the companies, like my college (Highline College) who bought Office 2019 (with Excel 2019) bought a dead version. Even though Office 365 version just came out, it has none of the new Array Functions, New Excel Calculation Engine or XLOOKUP. Microsoft says they will never add these to older versions. Only Office 365. It is Microsoft's goal to get everyone on Office 365. Here is the fact: We will all have to do this. Because the benefits of these new features far out weigh the costs of Subscription Based Office 365 . We will have to rise up from the ranks of the workers to inform the decision makers of the companies that we must move in that direction. It is to the benefit of the company and to us workers. So it may be a while until we are all on Office 365, but when we get there it will be a new and amazing world!!!!
@Hey_Delight5 жыл бұрын
ExcelIsFun Sir... with Xlookup maybe there'll be more workers who'll be enticed to use 365 but purchase of the 365 depends on management decision. And in my country, companies are mostly cost cutting, I'm pretty sure most of the companies would rather have the usual lifetime one time payment Excel or MsOffice rather than the 365 which requires a yearly subscription. But I do understand Sir, let's just hope for the best, and the best will be beneficial to all. Again, thanks so much for the free and updated tutorials.
@excelisfun5 жыл бұрын
@@Hey_Delight You are welcome! We can hope, though : ) That soon we will all have Office 365 and Excel Freedom : ) : )
@chrism90375 жыл бұрын
Excellent video Mike. Can't wait to get XLOOKUP!
@excelisfun5 жыл бұрын
I hope you will get it soon : ) Microsoft posted that it should be a few months...
@sasavienne5 жыл бұрын
I first liked, then commented to thank you in advance and now begins the fun of watching..🌟 🌟 🌟
@divinetouchtechnologies5 жыл бұрын
I like your step by step tuts
@excelisfun5 жыл бұрын
@@divinetouchtechnologies Gld you like the step by step, DIVINE TOUCH TECHNOLOGIES !!!!
@excelisfun5 жыл бұрын
Thank you for your amazing support, Salim : )
@richardhay6455 жыл бұрын
INDEX with MATCH has definitely met its match!! LOL. The "double XL" construction is about to become VERY familiar to Excel users. : )
@excelisfun5 жыл бұрын
Yes indeed! Love your term "Double XL" : ) : ) : ) : )
@wmfexcel5 жыл бұрын
Wow!!! Joining the lookup values and lookup arrays with &. That was a dream in VLOOKUP. XLOOKUP rocks! Thank you Mike for the demonstration of its power.
@marktheirl18424 жыл бұрын
I could not survive without your videos!
@DougHExcel5 жыл бұрын
another great use for XLOOKUP!
@excelisfun5 жыл бұрын
And... many more to come : ) Thanks, Teammate!!
@MalinaC5 жыл бұрын
XLOOKUP is amazing. Thanks for this example - so easy with XLOOKUP!
@excelisfun5 жыл бұрын
You are welcome for the XLOOKUP fun!
@simfinso8585 жыл бұрын
Xlook up is a Super thing.Thanks for posting Sir
@excelisfun5 жыл бұрын
You are welcome for the post! Is it in the Phone Version yet?
@simfinso8585 жыл бұрын
ExcelIsFun still Not available in phone Excel
@Excelambda5 жыл бұрын
as an insider I was lucky to have dynamic arrays like FILTER ,SORT ,UNIQUE for a while , but not yet XLOOKUP so manage to use as solution kind of nested FILTER as follows =FILTER(FILTER(D3:F6,(B3:B6=B9)*(C3:C6=C9)),D2:F2=D9). Mike, best Excel Expert EVER
@excelisfun5 жыл бұрын
LOVE your FILTER-FILTER!!!!! Thanks, cr gr0912!!!
@richardhay6455 жыл бұрын
When Mike did his first video on FILTER right after Dynamic Arrays were introduced in late 2018 he emphasized that FILTER should be considered to be an important Lookup Function. Probably FILTER should have been called FLOOKUP to distinguish it from the long-standing Filter and Advanced Filter Commands (NOT Functions) and to focus users on its roll as a Lookup Function!! I think FILTER is generally sold short and underutilized.
@excelisfun5 жыл бұрын
@@richardhay645 You are right that there are a few new funtions like FILTER that will change everything. I can't wait for Microsoft to release New Calc Engine to all of Office 365, so then I will have a new Excel Class Basics to Advance about Office 365 and show many new ways to do thinsg...
@richardhay6455 жыл бұрын
@@excelisfun The new calc engine will definitely revolutionize the teaching of Excel.
@sandravukovic29015 жыл бұрын
It's weird how happy this makes me :)))
@excelisfun5 жыл бұрын
Yes!!!!! Happy is VERY good : )
@fernando51662 ай бұрын
Learning more and more with the grandmaster, thank you very much
@sodelalbert2 жыл бұрын
OMFG I'm allways amazed how tohose excel folks are able to solve complex use cases witch such ease. I was looking for exatly that solution!!!
@excelisfun2 жыл бұрын
Glad to help : )
@maximpiras3 жыл бұрын
Hi Mike thanks for your amazing videos, been following you for a very long time;) Quick question relative to the index and match function. For the row argument why do you use INDEX again? That is, why this formula INDEX(D3:F6,MATCH(B9&C9,INDEX(B3:B6&C3:C6,),0),MATCH(D9,D2:F2,0)) instead of a slimmer INDEX(B3:F6,MATCH(B9&C9,B3:B6&C3:C6,0),MATCH(D9,B2:F2,0)) Also for the first array I always find easier to make reference to the entire table (B3:B6) instead of selecting only the values you'd like to return (D3:F6) as it doesn't matter as long the row/column intersection coming out from the formula is correct. Thanks
@wayneedmondson10655 жыл бұрын
Thanks Mike.. more fun showing the power and muscle of the new XLOOKUP function. Thumbs up!
@excelisfun5 жыл бұрын
Thanks for the powerful thumbs up, Wayne!!!!
@ogwalfrancis5 жыл бұрын
Great Step by Step explanation, Thank you so much Mr. Mike.
@excelisfun5 жыл бұрын
You are welcome, Ogwal!
@GeertDelmulle5 жыл бұрын
I love this time traveling: we can look so far into the future it’s incredible!... I wish the future had begun, already. :-)
@excelisfun5 жыл бұрын
I 100% agree. I wish the future would start RIGHT now!!!!!
@mohitgupta48105 жыл бұрын
Nice work of Xlookup.
@excelisfun5 жыл бұрын
Glad you like it, Mohit!
@mylenemalyon65385 жыл бұрын
I'm just started to learn vlookup, and we have xlookup now?! Whoah...
@excelisfun5 жыл бұрын
It is good to learn both, Mylene : )
@AndrewSeywright Жыл бұрын
Fantastic video, this helped me interact with a complex matrix at a data level. Now just need to figure out how to do this within PBI. Thank you so much
@excelisfun Жыл бұрын
In Power Query (tool in both Excel and Power BI Desktop) just create a new column that merges three fields into one to get a unique identifier, then you can merge to do a lookup.
@excelisfun Жыл бұрын
O, you must unpivot the first two fields first, then merge.
@excelisfun Жыл бұрын
Check this out! I NEVER make a reply video within 2 hours of posted comment. This is first time. Here is how to do it in Power Query: kzbin.info/www/bejne/fYfEiIZnmr6ViNE
@excelisfun Жыл бұрын
Dude!!!! You are a skateboarder? My son and I are both skaters and BMXers. We ride parks all the time!!! (I just posted this on your channel)
@AndrewSeywright Жыл бұрын
@@excelisfun fantastic thanks for this I’ll take a look now
@SyedMuzammilMahasanShahi5 жыл бұрын
EXCELlent video. Thanks Amazing Mike.
@excelisfun5 жыл бұрын
You are welcome, Syed : ) : )
@barrybrown21533 жыл бұрын
This was VERY helpful. I was able to expand into 3 lookups with one formula. Very well explained sir!
@excelisfun3 жыл бұрын
Glad this helps, Barry!
@mohamedchakroun49735 жыл бұрын
Bonus Formula are Awsome by getting bonus value to the EMT :-)
@excelisfun5 жыл бұрын
Glad you like the bonus formulas, Mohamed! They are fun : )
@papachoudhary54825 жыл бұрын
Thanks! Sir
@excelisfun5 жыл бұрын
You are welcome, PAPA!!!!
@artprintkyiv Жыл бұрын
Thank you for the detailed explanation!! Let me ask you how the formula will change if the data B9&C9, D9 is in one table and B3:B6&C3:C6... is in another table. Importrange?!?
@rajeshadoreswamy67782 жыл бұрын
Really great lesson this
@excelisfun2 жыл бұрын
Glad you like it!
@shuboliang26775 жыл бұрын
i have learnt a lot from your videos although i am not good at english
@excelisfun5 жыл бұрын
I am happy that you can learn from my videos, shubo!!!!!
@IbrahimAli-oj9ry5 жыл бұрын
Wow! great new function with amazing explanation. Thx MIKE (:
@excelisfun5 жыл бұрын
You are welcome, Ibrahim!!!
@johnborg60055 жыл бұрын
Thanks Mike. Great staff!!!!
@excelisfun5 жыл бұрын
You are welcome, John Borg!!!!
@BillSzysz15 жыл бұрын
Thanks :-) I found one unobvious interesting behavior of XLOOKUP. I think it can be a bit confusing to users. Later, I will send you a file with an explanation of what I mean.
@excelisfun5 жыл бұрын
I can't wait, Teammate Bill Szysz : ) : ) : ) : )
@pascaljoly57529 ай бұрын
I have noticed that you can only use a three-way lookup if the data is all on the same tab. If I have data on two separate tabs or spreadsheets, it won’t work. Can this be fixed? As a result, I am having to build a table that links the data I need from the source and then do my 3 way lookup.
@khanabdussabur86045 жыл бұрын
Awesome mike!
@excelisfun5 жыл бұрын
Glad it is awesome for you, Khan!!!
@samirkapadia34234 жыл бұрын
Hi Mike, First of all -- brilliant video. Have been following you for a couple of months now (since MSPTDA tutorials). Awesome, brilliant, OMG and wow are some words that come to mind when I see these videos. I have been grappling with a particular problem for some time now. My objective is to look up a table with 3-5 matching criteria (Client id, Invoice no, invoice month, invoice date [and if I could be so greedy the invoice amount]) - I was experimenting with "=XLOOKUP(B9&C9,B3:B6&C3:C6,XLOOKUP(D9,D2:F2,D3:F6))" and it works brilliantly with exact match. I was wondering if there was a way to add / use wildcards along with cells B9, C9 and D9. I have used advanced filters it works but it is not dynamic i.e. no auto-refresh and need to do Alt+A+Q every time I change the criteria. Being a bit lazy wanted more. I have used the new filter function (dynamic array) with the operators * and + (for some reason the ISBLANK [saw it in one of your EMT videos] has not worked well). Have met some success with (works in place of wildcards) =FILTER(Sheet1!E5:AJ25825,ISNUMBER(SEARCH(C4,Sheet1!E5:E25825))+ISNUMBER(SEARCH(D4,Sheet1!F5:F25825))+ISNUMBER(SEARCH(E4,Sheet1!G5:G25825))),":-(") But as I said the ISBLANK part hasn't worked when I tried a combination of * and + in the filter with multiple criteria Is there something that I have missed out - can you guide me. Thanks in advance.
@shahidjawaid61373 жыл бұрын
What if I have two lookup values, and lookup array in four column, how we will apply formula ?
@jessaleneetcobanez6818 Жыл бұрын
What if I have only 1 lookup value that can be found in multiple (columns) lookup array?
@dmclean33244 жыл бұрын
Is there a way to do this if the two criteria are numbers?
@abd-ix5qd4 жыл бұрын
Nice! What if I wanted to get all the return all values of that matches the multiple lookup criteria? Say,
@OldDirtyBoot5 жыл бұрын
Many thanks for the awesome training! My apologies if this has already been asked and answered, but I'm not sure I even know what to search for :) I was wondering if there was a formula (or method) for excel to auto-complete or auto-fill data pulled from another source when typing info into a cell? For example, If I have a table of wage information for a Journeyman (labeled: JM) that is seven columns wide, I'd like to simply type *JM* in the CLASS cell and have the wage information completed automatically. Again, my apologies if you've already covered this topic. Thanks!!
@excelisfun5 жыл бұрын
For back and forth dialog to get Excel solutions this is the best site I know: mrexcel.com/forum . 100s of Excel masters are waiting to answer if you provide enough detail.
@paulsingleton60714 жыл бұрын
Hi Mike, Happy New Year! Would you be able to do video from a slightly different angle? What if you have two possible answers? Can you put the second xlookup in the "if not found" argument of the first xlookup whilst looking up the latest entry by date? Thank you :-) ...sorry Mike I worked it out...it works as long as you get the "-1" in the search mode :-)
@excelisfun4 жыл бұрын
Awesome! That means you are a very smart Exceler : )
@msi________27602 жыл бұрын
Sir I need your help to clear one doubt
@shoppersdream3 жыл бұрын
Thanks! Why some are still using F4 to Lock but I also noticed that some are not using F4 for XLOOKUP?
@excelisfun3 жыл бұрын
Formulas that you manually copy require the different types of cell references using the F4 key. Spilled array formula that you will never manually copy, don't require locking and therefore you do not need to use the F4 key.
@shoppersdream3 жыл бұрын
@@excelisfun Thank you for the clarification.
@ericmoore3955 жыл бұрын
Hey Mike, Thanks for the awesome info. I have another problem but i am not sure how to post it to you. Please help. I sure could use some knowledge
@ericmoore3955 жыл бұрын
Basically, I am trying to to put in a cell the math formula to calculate Feet to Inches but it uses the same calculation for multiple items in a table. so instead of just selecting =sum(A1*12) I am trying to make ANY cell in a table go to that formula saying *12. Obviously the table is going to have all sorts of different measurements so its not All going to be feet to inches. But in this case, an if statement would determine it would go look for the feet to inches forumula. But I do not know how to input such an equation without the cell number to go get the info. I am sorry if this sounds confusing, but this is a real stickler for me to overcome. Thanks folks
@gintomino41365 жыл бұрын
This is AWESOME! wow
@excelisfun5 жыл бұрын
Glad you like it, Gin!!!
@msi________27602 жыл бұрын
I'm not able to make one file use this formula
@stevennye50754 жыл бұрын
thanks!
@excelisfun4 жыл бұрын
You are welcome, Steven Nye!!! Thanks for being a consistent support : ) : )
@ennykraft5 жыл бұрын
Great new function but I'm thoroughly fed up with Microsoft coming up with amazing new functions, promising they'll become available to all 365 users "in a few months" and then never delivering. We're still waiting for dynamic arrays that we were promised would become official in "early 2019". Now it's September and they're still not here. I can't use insider because I need an absolutely stable office that's the same as my students.
@excelisfun5 жыл бұрын
Me too. I was planning to create the New Excel Office 365 Class for my Highline College Excel Class, but until it is released to all, I can't do it... We are inconvenienced greatly. But in the long run, we should all be good because the product will be so helpful.
@luzaanvanwyk19774 жыл бұрын
Any indication when XLOOPUP will be available in Office 365? Seem like it's taking forever... Close to a year since you've posted this video... Desperate...
@excelisfun4 жыл бұрын
Microsoft says this July, the very month that we are in it will be released to everyone that has 365. This video shows the formula if you do not have XLOOKUP too : )