02:39 Bonus formula : )

Xlookup is nothing without teachers like Mike who explain how we can make the best use of it. Great job. Thank you...

@excelisfun

4 жыл бұрын

You are welcome for the teaching, Salim : ) : ) : ) : )

Superb formula. I have been following you for years, and anyone who joins my team gets sent straight to your tutorials

@excelisfun

6 ай бұрын

I am happy to help your Team : ) : ) Go Team!!!!!

This is the most helpful, concise tutorial on XLOOKUP that I could find on KZread. Thank you for posting this!

@excelisfun

6 ай бұрын

You are welcome!!!!

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

@excelisfun

4 жыл бұрын

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.

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.

@excelisfun

4 жыл бұрын

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_Delight

4 жыл бұрын

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.

@excelisfun

4 жыл бұрын

@@Hey_Delight You are welcome! We can hope, though : ) That soon we will all have Office 365 and Excel Freedom : ) : )

The fun never stops!

@excelisfun

4 жыл бұрын

NEVER!!!! : )

INDEX with MATCH has definitely met its match!! LOL. The "double XL" construction is about to become VERY familiar to Excel users. : )

@excelisfun

4 жыл бұрын

Yes indeed! Love your term "Double XL" : ) : ) : ) : )

Excellent video Mike. Can't wait to get XLOOKUP!

@excelisfun

4 жыл бұрын

I hope you will get it soon : ) Microsoft posted that it should be a few months...

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.

I first liked, then commented to thank you in advance and now begins the fun of watching..🌟 🌟 🌟

@divinetouchtechnologies

4 жыл бұрын

I like your step by step tuts

@excelisfun

4 жыл бұрын

@@divinetouchtechnologies Gld you like the step by step, DIVINE TOUCH TECHNOLOGIES !!!!

@excelisfun

4 жыл бұрын

Thank you for your amazing support, Salim : )

I could not survive without your videos!

Learning more and more with the grandmaster, thank you very much

Thanks Mike.. more fun showing the power and muscle of the new XLOOKUP function. Thumbs up!

@excelisfun

4 жыл бұрын

Thanks for the powerful thumbs up, Wayne!!!!

XLOOKUP is amazing. Thanks for this example - so easy with XLOOKUP!

@excelisfun

4 жыл бұрын

You are welcome for the XLOOKUP fun!

This was VERY helpful. I was able to expand into 3 lookups with one formula. Very well explained sir!

@excelisfun

3 жыл бұрын

Glad this helps, Barry!

another great use for XLOOKUP!

@excelisfun

4 жыл бұрын

And... many more to come : ) Thanks, Teammate!!

It's weird how happy this makes me :)))

@excelisfun

4 жыл бұрын

Yes!!!!! Happy is VERY good : )

Great Step by Step explanation, Thank you so much Mr. Mike.

@excelisfun

4 жыл бұрын

You are welcome, Ogwal!

Xlook up is a Super thing.Thanks for posting Sir

@excelisfun

4 жыл бұрын

You are welcome for the post! Is it in the Phone Version yet?

@simfinso858

4 жыл бұрын

ExcelIsFun still Not available in phone Excel

I'm just started to learn vlookup, and we have xlookup now?! Whoah...

@excelisfun

4 жыл бұрын

It is good to learn both, Mylene : )

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

@excelisfun

4 жыл бұрын

LOVE your FILTER-FILTER!!!!! Thanks, cr gr0912!!!

@richardhay645

4 жыл бұрын

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.

@excelisfun

4 жыл бұрын

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

@richardhay645

4 жыл бұрын

@@excelisfun The new calc engine will definitely revolutionize the teaching of Excel.

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

@excelisfun

2 жыл бұрын

Glad to help : )

Nice work of Xlookup.

@excelisfun

4 жыл бұрын

Glad you like it, Mohit!

Thanks Mike. Great staff!!!!

@excelisfun

4 жыл бұрын

You are welcome, John Borg!!!!

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

EXCELlent video. Thanks Amazing Mike.

@excelisfun

4 жыл бұрын

You are welcome, Syed : ) : )

I love this time traveling: we can look so far into the future it’s incredible!... I wish the future had begun, already. :-)

@excelisfun

4 жыл бұрын

I 100% agree. I wish the future would start RIGHT now!!!!!

Wow! great new function with amazing explanation. Thx MIKE (:

@excelisfun

4 жыл бұрын

You are welcome, Ibrahim!!!

Awesome mike!

@excelisfun

4 жыл бұрын

Glad it is awesome for you, Khan!!!

Really great lesson this

@excelisfun

2 жыл бұрын

Glad you like it!

Bonus Formula are Awsome by getting bonus value to the EMT :-)

@excelisfun

4 жыл бұрын

Glad you like the bonus formulas, Mohamed! They are fun : )

This is AWESOME! wow

@excelisfun

4 жыл бұрын

Glad you like it, Gin!!!

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.

Nice! What if I wanted to get all the return all values of that matches the multiple lookup criteria? Say,

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: kzread.info/dash/bejne/eYqVubVqlbzIgtI.html

@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

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

Thanks! Sir

@excelisfun

4 жыл бұрын

You are welcome, PAPA!!!!

thanks!

@excelisfun

4 жыл бұрын

You are welcome, Steven Nye!!! Thanks for being a consistent support : ) : )

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

@excelisfun

4 жыл бұрын

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.

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

@excelisfun

4 жыл бұрын

Awesome! That means you are a very smart Exceler : )

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.

i have learnt a lot from your videos although i am not good at english

@excelisfun

4 жыл бұрын

I am happy that you can learn from my videos, shubo!!!!!

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

@ericmoore395

4 жыл бұрын

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

Is there a way to do this if the two criteria are numbers?

What if I have two lookup values, and lookup array in four column, how we will apply formula ?

What if I have only 1 lookup value that can be found in multiple (columns) lookup array?

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.

@excelisfun

4 жыл бұрын

I can't wait, Teammate Bill Szysz : ) : ) : ) : )

Sir I need your help to clear one doubt

I'm not able to make one file use this formula

Thanks! Why some are still using F4 to Lock but I also noticed that some are not using F4 for XLOOKUP?

@excelisfun

3 жыл бұрын

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.

@shoppersdream

3 жыл бұрын

@@excelisfun Thank you for the clarification.

If u can help please let me know

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

@excelisfun

4 жыл бұрын

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

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.

@excelisfun

4 жыл бұрын

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.