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 😊
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.
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!!
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.
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.
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?
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.
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!
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
I'm having troubles in showing all the ranges (20 numbers), provided the formula to get the first number =XLOOKUP(L6,C:C,D:D), and last number =XLOOKUP(L6,C:C,D:D,"",2,-1), but what I want to display is the entire range, which is 20 numbers, can you help?
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
Question: I have an Excel invoice with hundreds of different items. How would I check that vendor is always charging the same price per item? What function would I use. Filter is too cumbersome. Thanks.
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, I have huge data exported which are in one row only. Say name, age, salary, allowance of 1000 employees but all in one row. I converted to text to column, but want in columnar format, say column A contains name, Column B gives age. pls advise
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.
Hi everyone shows the same example in the internet but nobody gives an example to make sum of the data with xlookup. If you have any idea please share in a video. My query is if we take your same example in case one name repeated in many columns and scores is it possible to find the total scores against the name? For example if the data if Greg name is repeated in A3, A5 and A7 cells and B3,5 and 7 his scores I want to sum of these three scores. Please assist.
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
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
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
I am using XLOOKUP formula to get my answer from another sheet. but i am typing same formula in each columns. I want to make XLOOKUP dynamic so i can type the formula in one column and drag the formula to other columns to get my answers. instead of typing same formula in all columns. Please help me. Thanks in advance.
i have a question. there are two tables (created with ctrl + t command) on the same sheet in first table there are two headings 1. subject 2. marking type (means particular subject will be awarded grades or marks) (for example there are four subjects i.e. ENGLISH , MATH, DRAWING, SPORTS, MUSIC) [FIRST TWO SUBJECTS WILL BE AWARDED MARKS AND REST OF THREE WILL BE GRADED] now there is second table having one column 1. grading subject aim of creating second table is to show only grading subjects from ist table WHICH ARE DRAWING SPORTS AND MUSIC XLOOKUP NOT WORKING IS THERE ANY OTHER OPTION?
Dear sir, I hv made one screener in Google sheet for stockss, where if I put stock name in formula, it produces one table for that stock and wether it for in criteria, I identify by seeing colour code change. Now if I have 50 stocks to check, I have to manually enter all the stocks one by one in formula, then going down by scrolling and checking for selection. Is any method available in Google excel sheet that one by one it run and check all the stocks from the list and make a new list for filtered stock in Google spreadsheet. Kindly help, I am trying from last 1 week but not getting. Waiting for your reply, A subscriber of channel 🙏
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 🙏👍
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
![Twenty One Pilots - “The Line” (from Arcane Season 2) [Official Music Video]](http://i.ytimg.com/vi/E2Rj2gQAyPA/mqdefault.jpg)
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 !
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.
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 😊
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 :)
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!!
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.:)
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.
a beginner in excel and wow!!! xlook up is made easy! thanks you!
Thanks for the clear and detailed explanation of the Xlookup function
Sumit, What an awesome video about the fabulous new XLOOKUP excel function. Thank you SO MUCH
Glad you liked it Pat!
Great Work..4E... excellently explained entire entries
You are very great explainer in ever Excel videos
so much effort I could have avoided had I know. Thanks
Very clear and very well explained, absolute beauty!
Thank you for sharing this! I have a "value" error problem, but I dont know how to fix. Can you advise? Thanks
Well explained with different examples. Thank you
Amazing video - the best on this topic. Thanks for posting it.
Can I like this video twice?
Superb presentation .......
Hi Sumit, thank you - i found your video extremely useful!
Wow. Great function. Very nice explanation. Thank you so much
Glad you liked it :)
Nicely done, Sumit. Thank you for the instruction.
Excellent information about xlookup function thankyou very much sir.
Explained very well.... Many thanks.
Very well explained. Nice
Waou !! Amazing presentation. Thank you for your great explanation.
Thanks a lot for your guidance 🙏👍❤️❤️❤️
Great video - I'm guessing those thumbs down videos are other Excel RUclips posters...?
Brilliant video Sumit
Nice Job Sumit . Please Is it possible to return a data in Excel base on Selection
Great, thanks a lot its really very helpful, explained so nicely and in layman which is easily understanable
thank you! I finally figured my formula out.
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?
More than perfect.. well done..
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.
Excellent job sir
Sumit, Thanks for sharing your knowledge! Your explanations were very clear.
Hi very nice video, however I am unable to download the file used as example
Gr8, very good video explaining various uses of xlookup 👍👍
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 u... Very well explained
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
Great video. Thank you.
Thank you so much for sharing
Very helpful video sir thank u
Awesome. Hands up. Keep it up
Very useful information
Great video buddy
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 :)
I'm having troubles in showing all the ranges (20 numbers), provided the formula to get the first number =XLOOKUP(L6,C:C,D:D), and last number =XLOOKUP(L6,C:C,D:D,"",2,-1), but what I want to display is the entire range, which is 20 numbers, can you help?
17:52 ____How Brilliant🤯
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.
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
How ahout multiple look-up cakes???. Could you please illustrate that with an example.
Question: I have an Excel invoice with hundreds of different items. How would I check that vendor is always charging the same price per item? What function would I use. Filter is too cumbersome. Thanks.
Really great tutorial thanks
Glad you liked it Brain!
AMAZING video!!! OMG! Thanks a bunch, Master!!!!
This was great. Thank you so much!
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’?)
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?
What about if there are more than one highest scorer with same mark in one subject?
Can you please upload your file again since I just can't download it? It blinks and then it does nothing. Thanks
Thank you very much.
How to search in a range of Data 2 columns but the return range is only 1 column ?
Hi Sumit,
I have huge data exported which are in one row only. Say name, age, salary, allowance of 1000 employees but all in one row. I converted to text to column, but want in columnar format, say column A contains name, Column B gives age. pls advise
Very useful bro keep it up
Can u create video using same formula for duplicate values.? How can we find output if lookup value has duplicates?
Hi! how can I do this? "The scope is to match items from column A in Tab xxxxx to the items from column B from Tab yyyyyy???
.
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.
Thank you for your efforts
Glad you found it useful!
Great learning.. Thanks
i got error #NA while using . By the way i have office 365. is it because i opened multiple sheets in a single file?
Hi everyone shows the same example in the internet but nobody gives an example to make sum of the data with xlookup. If you have any idea please share in a video. My query is if we take your same example in case one name repeated in many columns and scores is it possible to find the total scores against the name? For example if the data if Greg name is repeated in A3, A5 and A7 cells and B3,5 and 7 his scores I want to sum of these three scores. Please assist.
Thanks, Very helpful
Always great video ♥
Power Pivot:
Please do a series on Piwor Pivot. Thanks.
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
I liked the video. It was very educational but the ads were too much.
Thank you Sir.
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
A crear explanation Sumit. Thank you very much!
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.
well explained, thank you very much
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
👌👌
Very good
The link for example down load not working
I am using XLOOKUP formula to get my answer from another sheet. but i am typing same formula in each columns.
I want to make XLOOKUP dynamic so i can type the formula in one column and drag the formula to other columns to get my answers. instead of typing same formula in all columns.
Please help me.
Thanks in advance.
i have a question.
there are two tables (created with ctrl + t command) on the same sheet
in first table there are two headings
1. subject
2. marking type (means particular subject will be awarded grades or marks)
(for example there are four subjects i.e. ENGLISH , MATH, DRAWING, SPORTS, MUSIC)
[FIRST TWO SUBJECTS WILL BE AWARDED MARKS AND REST OF THREE WILL BE GRADED]
now there is second table having one column
1. grading subject
aim of creating second table is to show only grading subjects from ist table WHICH ARE DRAWING SPORTS AND MUSIC
XLOOKUP NOT WORKING
IS THERE ANY OTHER OPTION?
Dear sir,
I hv made one screener in Google sheet for stockss, where if I put stock name in formula, it produces one table for that stock and wether it for in criteria, I identify by seeing colour code change. Now if I have 50 stocks to check, I have to manually enter all the stocks one by one in formula, then going down by scrolling and checking for selection. Is any method available in Google excel sheet that one by one it run and check all the stocks from the list and make a new list for filtered stock in Google spreadsheet.
Kindly help, I am trying from last 1 week but not getting. Waiting for your reply, A subscriber of channel 🙏
Thank you master guru
thanks, helped a lot
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 🙏👍
many many thanks
Is it capture data from different sheet also formula in one sheet and input in other sheet
Yes it can
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
Thank you very much for all the great information.
Thank you very much for this super cool video, for this valuable and practical information, and for this unique approach.
Sir, can we use this in Google sheets?