How to use the XLOOKUP Function in Microsoft Excel - Beginners Tutorial
ฝัง
- เผยแพร่เมื่อ 21 ก.ค. 2024
- This video tutorial will show you how to use XLOOKUP in Microsoft Excel. The XLOOKUP Function can replace the VLOOKUP and HLOOKUP functions in Microsoft Excel. In this tutorial, I will show you why to XLOOKUP and give you 5 examples to see it in action. From a simple exact match to nested XLOOKUPs with the SUM function.
0:00 Introduction
0:50 Why use XLOOKUP in Microsoft Excel
1:38 Find exact match with XLOOKUP
3:35 Find multiple items with XLOOKUP
6:56 Add more arguments to XLOOKUP function
9:46 Used nested XLOOKUP to perform vertical and horizontal match (replace HLOOKUP)
13:54 Use the SUM function with nested XLOOKUP
Practice Worksheets: leveragingdigitalinc-my.share...
Great to have the privilege of a very recent Excel tutorial from you sir!
Perfectly explained! Every example was really useful, specially the one with the nested xlookup.
Sir, Thank you! I was searching for nested xlookup ...was great help.
Finished the playlist. Had a fun learning experience Jamie. thanks much
I was struggling with this and watched a few videos. I fixed my spreadsheet 3:45 seconds into watching your video! Thank you so much. I will be saving and referencing this video in the future and will recommend to others!
This was sup much more helpful than the other videos that I watched on this. Thank you!
GREAT VIDEO , WE CAN APPLY THIS TO OUR DIFFRENT KIND OF WORK THANKYOU
Thank you .. for this very good clip with downloadable example ,
Thank you, Sir.🥰😍🤩
Thank you for sharing your knowledge 💪
My pleasure!
Thank you for your informative videos. I have a particular case in excel in which I couldn’t find any solution to it and I hope you can help me with it. If I have various numbers in column B, and I need these numbers to be (increased/decreased):
• By 0.5 either to make it 20.5 or 19.5
• Or by 1 to make it either 21 or 19
• Or 2 to make it 22 or 18 …etc.
• The change should be based on two conditions, a selected text from a drop-down list in the next cell in column C in addition to a number I type in the cell next to them in column D, for example:
• The first case scenario is: When I type the number 20 in cell B2 and then I choose the text “increase” from a drop-down list in cell C2 and then I type 0.5 in the cell D2, I want this “increase” to be reflected on B2 and make it 20.5
• The second case scenario: When I type the number 15 in cell B3 and then I choose the text “decrease” from a drop-down list in cell C3 and then I type any number like 1, 2 or 3 in the cell D3, I want this “decrease” to be reflected on B3 and make it 14, 13 0r 12
• The third case scenario: When I type number 10 in cell B4 and then I don’t choose any text from a drop-down list in cell C4 and leave it blank with cell D4, I don’t want any change to be reflected on B4 and leave it 10 as it is.
I hope you can find time to answer this very soon. Thanks again.
Great! Thanks.
This is so awesome
Thank you!
You're amazing
Hi thanks for the xlookup video. I have a data list of employees and there are some duplicates as someone terms out of division 1 and is rehired into division 2 and I need to add their income while in each division so that I have a full picture of their income items. So its a matter of finding a duplicate person and then adding each column separately so I know what there regular earnings are in total and then what their 401(k) withholding was in total, etc. I was unable to manipulate your sum tab xlookup formula to do the job for me. Thanks! Also how do you do that summary first tab?
Hello dear Mr. Jamie, I have a question... If in the Finds Matching worksheet you put in bigger value as an "Income" than the highest 575000 Dollar for instance 600000 Dollar than the tax rate goes to 0%. I don't think that it should be like that. How can you solve this problem? Is there something else that should be done with the formula in that case?
My Excel version doesn't seem to have UNIQUE and XLOOKUP formulas. What version are you using sir? 😅
When will you create MS excel advance level ?
Thanks for all amazing training videos, short, sweet and right to the point but very through! Would you kindly let me know how I can use XLOOKUP up to search for the number of rows with a field includes certain value for This and/ or Last Week, This and/ or Last Month, This and/ or Last Quarter and This and/ or Last Year? How would the formula could look? Do you know a better solution without NO VBA? Thank you!
Try using the =countif function instead
Great video Sir. I wanted to follow along in the workbook but it was locked to read-only :(. Couldnt edit or download.
Thanks, I’ll check the settings
Great!! But wild card was missing..
Is there an Hlookup tutorial coming soon?
and you do a great job and put allot of effort and patience in your videos
Thank you!
I didn't understand the exercise of the NEsted Lookup function with $ sign, can you put it in another video and explain it more in detail? It's very useful but my Excel is not working since I checked all the steps of the syntax...but it doesn't work..and I don't get why dragging on the right it should change automatically the Qrt ... My mind just exploded !!!!
Hi, I can look at doing a detailed video on this. Thanks for the feedback!
@@TeachersTechTHANKS A LOT FOR ANSWERING!!!
I'd like to understand the meaning of dragging right a cell with a look up function and in general I know the meaning of $ sign but in this case , mixing 2 v look up i got confused...
I hate to be that guy, but, the profit % at the bottom isn't adding up for me, the numbers look off. What am I doing wrong?
Nested part kinda confused me so i make xlookup for each cell of gross,net, profit% and it works lol
breaking bad is your employees wowwww
Dang...I don't have xlookup. I do have vlookup though.
Better to use Index + Match then
can you show how to make a free website
Sir, i used xlookup nested video all fine, but when i enter if_not_ found or match_ mode, no effect' =XLOOKUP(A7,GRAM!A2:A20,XLOOKUP($A$1,GRAM!B1:F1,GRAM!B2:F20,,-1)), if i use without nested '=XLOOKUP(F7,price!$A$2:$A$220,price!$B$2:$B$220,,-1) it works. Kindy advise.