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!!
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?
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.
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
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
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
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?
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.
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
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
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
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 🙏
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).
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?
Kindly help me with this... i need to arrange few items in such a way that if i choose any item i need few items to be populated in front of it Example: if i choose laptop a list of items need to populate like charger, speaker, mouse related accessories What function can do this ???? Please help
@@trumpexcel But in Office 365 this can be done easily by using FILTER function where results will appear in multiple cells (vertically) and in combination with TEXTJOIN function where results will appear in single cell separated by comma or TRANSPOSE function if you want each value in separate cell (horizontally)
@@sandip_bettereveryday Yes, O365 makes a lot of complex formulas used earlier easier. The article I shared is for those without access to O365 (will need to update it for these new functions though)
Hi Sumit, Thank you very much for the valuable info on the Xlookup function. I have done a lot of work using the info you have provided. However I have one question, how do I get rid of the #Spill error that happens when Im working with a Table format (does not happen when I conver the Table to a Range). I looked up Google for an answer and it clearly says that the #Spill error is not supported in Excel Tables. Just wanted to know if you have work around?? here is the extract from Google."Spilled array formulas aren't supported in Excel tables. Try moving your formula out of the table, or converting the table to a range (click Table Design > Tools > Convert to range)."Awaiting your response.Best Regards Aspi.
If I use Xlookup in office 365 and if someone opens the same file in older versions where it is not available, then will the formula give an error or will it still work ?
Brother your information is very good but we can not see the actual formula coz your subtitles are hiding all the info which you are showing in the sheet. However please remove the subtitles from your screen.
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 🙏👍
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 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.
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.
