I’m learning excel and you explain everything to clearly! Thank you. I haven’t found a video yet that shows how to pull data from a different sheet using xlookup. Trying this tomorrow! Subscribed to learn more 😊
Very helpful, and very clear. I am going to start converting some VLOOKUPs right away because I can see X removing quite a few potential issues down the road.
OMG.. You made my work a breeze. I had wanted to find a formula which would return the most recent instance of lookup value and couldn't find any video which explained it. Thought would have to sort all my data in descending order to get it but that would mean sorting each time I added to the table 😅. This video gave me exactly what I wanted without messing with the order. Thank you so very much!!
If the F4 key also has an icon on the key, it indicates the key has a secondary function. To use the secondary function, press and hold down the Fn key, and while continuing to hold the key, press F4
I double clicked on the fill handle. When you select a cell, you will notice a small green square at the bottom right. If you double click on it, it fills the column (based on how far the cells are filled in the adjacent column)
@@trumpexcel There’s also a keystroke for this. I just can’t find it. I use more keystrokes in Office than I do mouse (sometimes mouse plus key). I especially like the shift-space to select a row for doing headers (enter moves to the right instead of down).
@@garypoplin4599 The one I know is to select all the cells in which you want the formula result, then enter the formula in the active cell and then hold the Control key and press Enter. This will flll the formula in all the selected cells
Finally I got it to work. I created a new sheet and then it worked. thanks. Can you please go slower next time, and don't assume people know where you click. Never assume.
Helpful isn't the word. I had to scrub a warehouse inventory of 1,565 individual parts against a bill of materials in excess of 2,500 lines. This video just saved my soul from destruction.
Thank you so much for the explanation. I have done a look-up for the entire value (around 5:22min). However the results are horizontal and I would like to have them vertically. How can I transpose the column? Thank you in advance.
Copy the entire table and then taste special by pressing ctrl+alt+v and you will a find called transpose click on transpose. Now you data will convert vertically
Great tutorial! With the power of Excel 365, I have to believe the Excel C++ code would probably go to the moon and back! Next release Excel will probably ask: "Want a coffee with that spreadsheet"? LOL
If you can open all the files with Excel 365, you can use it. XLOOKUP doesn't have backward compatibility though, which means that if you open a workbook which has XLOOKUP in a version that doesn't have XLOOKUP, it will show an error
What about when looking up a record that has multiple entries? - For example, what if there were three Greg names in the file, and you wanted to see all three, but still only show 1 name if there were 1 Amy match, or 2 names if there were two Stan matches?
You can create a custom function with VBA that does some of what XLOOKUP can do. I am not sure if you can get all the functionality of XLOOKUP with VBA
Hi, This was really useful. But all the examples had names as unique identifiers - i.e. there was only 1 Greg and 1 Amy. Is this useful of you have three Gregs? It will be really helpful to understand if the unique identifier is duplicated, what are the options. Thanks!
Got a question. What if I wanted to search for a value in multiple non continuous columns and return they adjacent cell. For example, look up a value in a1 and look up array in b1 to b5 and return c1 to c5 but also do the same for a second lookup array in e1 to e5 with return f1 to f5. So if value from a1 is found in b OR e it will return whats in c or f. Hope that makes sense.
I really enjoyed your video. I have been working on a menu planning project and have run into a brick wall. In your video on XLOOKUP, you mentioned that a single search can return multiple records. I'm trying to do just that.Can a single drop down item return several items from other data sets? Example: In a Breakfast category selecting "Breakfast Burritos" I would need to return its ingredients from three data sets into cells: items from a Protein data set, items from a Carbs data set and items from a Fats data set. Using the Burrito example, Eggs would be pulled from the Protein set, Wheat Tortilla from the Carbs set and, Sausage and Cheese from the Fats set and all placed in specifically selected cells. Other drop down selections would pull their ingredients from the appropriate lists. Thank you.
Please show how to analyse a personal bank statement in Excel into Account heads like Bank Int.,Salary,bank charges, family transfer,interbank TRF.,etc. along with Account Group like Income,Exps.,Bank transfers,etc. asap.thanks Bansalji 🙏👍
Thanks for the video. How can we reference the return array from another sheet if we know the number of the column? Like if I am in one sheet and want to return the second column (2) from another sheet (sheet!B:B)? I want column B:B from the second sheet being linked to a number, so became dinamic? Is it possible?
Hi Sumit, Thanks a lot for the Great information & The wonderful explanation simplified but when I added "Didn't Appear" even if the searchable value is available the formula result is # value. How I can solve? please help
Hi Sumit.. thanks for the excellent video. Lots of great tips and tricks in there.. especially nesting XLOOKUP in the if_not_found argument.. very clever. Thanks for sharing. Thumbs up for Trump Excel!
Thanks Wayne.. I finally decided to join the insider program and got access to these cool new functions. This really makes a lot of stuff easier, especially making dashboards.
@@trumpexcel Hi Sumit.. thanks.. I've been tinkering with them on Insider for a few months. But, just last week they were enabled in my standard Office 365 subscription.. including XLOOKUP and XMATCH. So, the rollout is happening. I'm glad to have them in my regular subscription version, as now I can reliably incorporate their use into my daily activities vs. just on a test system. Thanks again for all your great videos.. I learn a lot from your channel and your web site.. and regularly recommend them to anyone who asks me about learning EXCEL. Thumbs up!
I have an ecxcel sheet with annual summaries of some figures which are against separate account headings. Max value I can find but i want to see this amount is against which heading. Xlookup can be used if you know the searching array. Here i can find it manually but need it be by excel. Hope u got my problem. Eg 2018, 2019 etc separate(exp income profit etc under each year).
Thank you! The previous videos on the same subject I just watched were unintelligible. Your explanation was much clearer and easier to understand.
I can not express how much I appreciate your work, Thank you very much Sumit !
I’m learning excel and you explain everything to clearly! Thank you. I haven’t found a video yet that shows how to pull data from a different sheet using xlookup. Trying this tomorrow! Subscribed to learn more 😊
Your communication skill and teaching skill both are excellent
Best video on the XLookup that I have seen. A must see. Honestly, I love all of his videos. But this one is my favorite of all of his work.
Very helpful, and very clear. I am going to start converting some VLOOKUPs right away because I can see X removing quite a few potential issues down the road.
This is new for me. I'm thankful to see your video as i learn one new option today.:)
Sumit, What an awesome video about the fabulous new XLOOKUP excel function. Thank you SO MUCH
Glad you liked it Pat!
OMG.. You made my work a breeze. I had wanted to find a formula which would return the most recent instance of lookup value and couldn't find any video which explained it. Thought would have to sort all my data in descending order to get it but that would mean sorting each time I added to the table 😅. This video gave me exactly what I wanted without messing with the order. Thank you so very much!!
Thanks a lot for your guidance 🙏👍❤️❤️❤️
Great Work..4E... excellently explained entire entries
Wow. Great function. Very nice explanation. Thank you so much
Glad you liked it :)
a beginner in excel and wow!!! xlook up is made easy! thanks you!
Thanks for the clear and detailed explanation of the Xlookup function
Very clear and very well explained, absolute beauty!
Waou !! Amazing presentation. Thank you for your great explanation.
You are very great explainer in ever Excel videos
Sumit, Thanks for sharing your knowledge! Your explanations were very clear.
Amazing video - the best on this topic. Thanks for posting it.
Well explained with different examples. Thank you
Explained very well.... Many thanks.
Hi Sumit, thank you - i found your video extremely useful!
Nicely done, Sumit. Thank you for the instruction.
Great, thanks a lot its really very helpful, explained so nicely and in layman which is easily understanable
Superb presentation .......
Excellent information about xlookup function thankyou very much sir.
so much effort I could have avoided had I know. Thanks
Gr8, very good video explaining various uses of xlookup 👍👍
17:52 ____How Brilliant🤯
Thank u... Very well explained
Very well explained. Nice
More than perfect.. well done..
Good presentation. At 13:25 could you clarify the "Locking" using F4? Why it is done?
"locking" keeps the locked reference the same even if you copy the formula down, or over.
I am pressing F4 but it is not locking. What am I doing wrong? Please help.
If the F4 key also has an icon on the key, it indicates the key has a secondary function. To use the secondary function, press and hold down the Fn key, and while continuing to hold the key, press F4
Superb source of very valuable advice. Thanks very much. Bye-Bye Vlookup !
While I am huge a fan of VLOOKUP and INDEX/MATCH, this indeed is Bye-Bye VLOOKUP :)
16:30 - Wow! That was cool! What keystroke did you use to get that formula to fill down!?
I double clicked on the fill handle. When you select a cell, you will notice a small green square at the bottom right. If you double click on it, it fills the column (based on how far the cells are filled in the adjacent column)
@@trumpexcel There’s also a keystroke for this. I just can’t find it. I use more keystrokes in Office than I do mouse (sometimes mouse plus key). I especially like the shift-space to select a row for doing headers (enter moves to the right instead of down).
@@garypoplin4599 The one I know is to select all the cells in which you want the formula result, then enter the formula in the active cell and then hold the Control key and press Enter. This will flll the formula in all the selected cells
@@trumpexcel _Thats_ the one I was hunting! Thanks.
Excellent job sir
Thank you very much for this video. Please make a video on how to fetch all student's name who scored the highest marks @22:33
Thank you very much for this super cool video, for this valuable and practical information, and for this unique approach.
Finally I got it to work. I created a new sheet and then it worked. thanks. Can you please go slower next time, and don't assume people know where you click. Never assume.
AMAZING video!!! OMG! Thanks a bunch, Master!!!!
thank you! I finally figured my formula out.
Really great tutorial thanks
Glad you liked it Brain!
Thank you so much for sharing
A crear explanation Sumit. Thank you very much!
Great video. Thank you.
This was great. Thank you so much!
Great video - I'm guessing those thumbs down videos are other Excel UA-cam posters...?
Helpful isn't the word. I had to scrub a warehouse inventory of 1,565 individual parts against a bill of materials in excess of 2,500 lines. This video just saved my soul from destruction.
I know the feeling. Glad to the know the video helped :)
Very helpful video sir thank u
Thank you so much for the explanation.
I have done a look-up for the entire value (around 5:22min). However the results are horizontal and I would like to have them vertically. How can I transpose the column?
Thank you in advance.
Copy the entire table and then taste special by pressing ctrl+alt+v and you will a find called transpose click on transpose. Now you data will convert vertically
Thank you for your efforts
Glad you found it useful!
Great learning.. Thanks
Very useful information
well explained, thank you very much
Nice Job Sumit . Please Is it possible to return a data in Excel base on Selection
Brilliant video Sumit
Thank you very much for all the great information.
Always great video ♥
Thanks, Very helpful
Great tutorial! With the power of Excel 365, I have to believe the Excel C++ code would probably go to the moon and back! Next release Excel will probably ask: "Want a coffee with that spreadsheet"? LOL
This is great information. Can you use xlookup in Excel 365 to find values in other versions of Excel?
If you can open all the files with Excel 365, you can use it. XLOOKUP doesn't have backward compatibility though, which means that if you open a workbook which has XLOOKUP in a version that doesn't have XLOOKUP, it will show an error
Thank you for sharing this! I have a "value" error problem, but I dont know how to fix. Can you advise? Thanks
Thank you Sir.
What about when looking up a record that has multiple entries? - For example, what if there were three Greg names in the file, and you wanted to see all three, but still only show 1 name if there were 1 Amy match, or 2 names if there were two Stan matches?
Hi Sumit,
Please tell how we can add xlookup in our existing excel using VBA. Thanks
You can create a custom function with VBA that does some of what XLOOKUP can do. I am not sure if you can get all the functionality of XLOOKUP with VBA
@@trumpexcel Thanks for replying. Please make a video on the same.
Awesome. Hands up. Keep it up
Thank you master guru
thanks, helped a lot
9:28 - How can I use this functionto optionally add the record ‘Sam’? (I.e., pop up a dialog with the message “Do you want to add ‘Sam’?)
Hi, can u please make a video on how to fetch student's name who scored more than 99.. @22:33
many many thanks
Great video buddy
Very useful bro keep it up
Power Pivot:
Please do a series on Piwor Pivot. Thanks.
Hi, This was really useful. But all the examples had names as unique identifiers - i.e. there was only 1 Greg and 1 Amy. Is this useful of you have three Gregs? It will be really helpful to understand if the unique identifier is duplicated, what are the options. Thanks!
Thank you!!!!
Got a question. What if I wanted to search for a value in multiple non continuous columns and return they adjacent cell. For example, look up a value in a1 and look up array in b1 to b5 and return c1 to c5 but also do the same for a second lookup array in e1 to e5 with return f1 to f5. So if value from a1 is found in b OR e it will return whats in c or f. Hope that makes sense.
I really enjoyed your video. I have been working on a menu planning project and have run into a brick wall. In your video on XLOOKUP, you mentioned that a single search can return multiple records. I'm trying to do just that.Can a single drop down item return several items from other data sets? Example: In a Breakfast category selecting "Breakfast Burritos" I would need to return its ingredients from three data sets into cells: items from a Protein data set, items from a Carbs data set and items from a Fats data set. Using the Burrito example, Eggs would be pulled from the Protein set, Wheat Tortilla from the Carbs set and, Sausage and Cheese from the Fats set and all placed in specifically selected cells. Other drop down selections would pull their ingredients from the appropriate lists. Thank you.
👌👌
Very good
The link for example down load not working
Very Interesting
Great examples! Thank you :-)
Hi very nice video, however I am unable to download the file used as example
What about if there are more than one highest scorer with same mark in one subject?
Thanks
Hi Sumit, it's really amazing. I just have a query - Can we get value applying multiple criterion, as we do in Indexmatch!
nice sir ji.
Can u create video using same formula for duplicate values.? How can we find output if lookup value has duplicates?
Please show how to analyse a personal bank statement in Excel into Account heads like Bank Int.,Salary,bank charges, family transfer,interbank TRF.,etc. along with Account Group like Income,Exps.,Bank transfers,etc. asap.thanks Bansalji 🙏👍
Thank you very much.
How to search in a range of Data 2 columns but the return range is only 1 column ?
Thanks for the video. How can we reference the return array from another sheet if we know the number of the column? Like if I am in one sheet and want to return the second column (2) from another sheet (sheet!B:B)? I want column B:B from the second sheet being linked to a number, so became dinamic? Is it possible?
How ahout multiple look-up cakes???. Could you please illustrate that with an example.
Can I like this video twice?
Is it capture data from different sheet also formula in one sheet and input in other sheet
Yes it can
Super sir
Hi Sumit, Thanks a lot for the Great information & The wonderful explanation simplified but when I added "Didn't Appear" even if the searchable value is available the formula result is # value. How I can solve? please help
Hi Sumit.. thanks for the excellent video. Lots of great tips and tricks in there.. especially nesting XLOOKUP in the if_not_found argument.. very clever. Thanks for sharing. Thumbs up for Trump Excel!
Thanks Wayne.. I finally decided to join the insider program and got access to these cool new functions. This really makes a lot of stuff easier, especially making dashboards.
@@trumpexcel Hi Sumit.. thanks.. I've been tinkering with them on Insider for a few months. But, just last week they were enabled in my standard Office 365 subscription.. including XLOOKUP and XMATCH. So, the rollout is happening. I'm glad to have them in my regular subscription version, as now I can reliably incorporate their use into my daily activities vs. just on a test system. Thanks again for all your great videos.. I learn a lot from your channel and your web site.. and regularly recommend them to anyone who asks me about learning EXCEL. Thumbs up!
In ex 10 can you pls explain why you used 2 commas at the end of the formula before clicking on wild card character. Thanks
Very good !
I have an ecxcel sheet with annual summaries of some figures which are against separate account headings. Max value I can find but i want to see this amount is against which heading. Xlookup can be used if you know the searching array. Here i can find it manually but need it be by excel. Hope u got my problem. Eg 2018, 2019 etc separate(exp income profit etc under each year).
Still waiting for your response❤
Superb