10 Excel XLOOKUP Function Examples (Better than VLOOKUP & INDEX/MATCH)
HTML-код
- Опубликовано: 8 июл 2024
- In this video, I will show you what is the new XLOOKUP function and 10 XLOOKUP examples.
Get Office 365 - microsoft.msafflnk.net/Vk9OR
Note that this function is only available in Office 365 (Home, Personal and University edition) at the time of recording this video. It will likely be made available to all Office 365 users.
Download the Example file: bit.ly/2tyQw7I
The following XLOOKUP examples are covered in this video:
1. SIMPLE LOOKUP (Lookup and fetch a value): In this example, I will show you how to use XLOOKUP to look for value and fetch it. It can fetch the value from the right or the left of the lookup value (something VLOOKUP isn't made to it)
2. LOOKUP AND FETCH THE ENTIRE RECORD: XLOOKUP can be used to look for value and fetch the entire record (entire row or column) from the return_array
3. TWO WAY LOOKUP: You can use two XLOOKUP functions together to get the two-way lookup (i.e, look for a value that meets two lookup criteria)
4. ERROR HANDLING IN XLOOKUP: Error handling in in-built in XLOOKUP and there is a dedicated argument where you can specify what you want in case there is an error. This could be a value, a cell reference or another formula. This a major improvement over using the VLOOKUP and IFERROR combination in the past
5. NESTED XLOOKUP: You can nest multiple XLOOKUP functions together to do a multi-level lookup. For example, you can look through multiple tables (in the same or separate worksheet) and fetch the value.
6. FIND THE LAST MATCHING VALUE: Again something that has been baked into XLOOKUP. You can choose the direction of the lookup. So you can fetch the first matching value or the last matching value
7. APPROXIMATE MATCH: There is also the approximate match where you can choose where you want the values just smaller/larger than the lookup value
8. HORIZONTAL LOOKUP: With XLOOKUP, you can do vertical lookup as well as horizontal lookup
9. CONDITIONAL LOOKUP: You can combine XLOOKUP with other formulas to do a conditional lookup. For example, if you want to look up what person has the maximum salary, you can do that easily using XLOOKUP with MAX.
10. WILDCARD LOOKUP: XLOOKUP can handle wildcard characters, but you need to specify that you will be using these. This example shows how to can do a partial lookup with wildcards.
Vlookup Video - • VLOOKUP Formula in Excel
VLookup with Multiple Criteria - • Using Excel VLOOKUP Fu...
Free Excel Course - trumpexcel.com/learn-excel/
Paid Online Training - trumpexcel.com/excel-training/
Best Excel Books: trumpexcel.com/best-excel-books/
⚙️ Gear I Recommend:
Camera - amzn.to/3bmHko7
Screen Recorder - techsmith.z6rjha.net/26D9Q
USB Mic - amzn.to/2uzhVHd
Wireless Mic: amzn.to/3blQ8uk
Lighting - amzn.to/2uxOxRv
Subscribe to get awesome Excel Tips every week: ruclips.net/user/trumpexc...
Note: Some of these links here are affiliate links!
#Excel #ExcelTips #ExcelTutorial
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 !
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.
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 😊
Thanks for the clear and detailed explanation of the Xlookup function
Sumit, Thanks for sharing your knowledge! Your explanations were very clear.
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!!
Thank you very much for this super cool video, for this valuable and practical information, and for this unique approach.
Your communication skill and teaching skill both are excellent
AMAZING video!!! OMG! Thanks a bunch, Master!!!!
Amazing video - the best on this topic. Thanks for posting it.
Thanks, I found to be a wonderful and valuable information.
Very clear and very well explained, absolute beauty!
Thank you very much for all the great information.
Nicely done, Sumit. Thank you for the instruction.
This is new for me. I'm thankful to see your video as i learn one new option today.:)
A crear explanation Sumit. Thank you very much!
Sumit, What an awesome video about the fabulous new XLOOKUP excel function. Thank you SO MUCH
Glad you liked it Pat!
a beginner in excel and wow!!! xlook up is made easy! thanks you!
Explained very well.... Many thanks.
thank you! I finally figured my formula out.
Well explained with different examples. Thank you
Thank u... Very well explained
This was great. Thank you so much!
so much effort I could have avoided had I know. Thanks
Always great video ♥
Hi Sumit, thank you - i found your video extremely useful!
well explained, thank you very much
This is so helpful! Thank you very much.
Thanks so much for the tutorial...
its very helpful
Wow. Great function. Very nice explanation. Thank you so much
Glad you liked it :)
Thank you very much for your such valuable explanation. Great!
Thanks heaps. Well explained.
More than perfect.. well done..
Great Work..4E... excellently explained entire entries
Thanks a lot for your guidance 🙏👍❤️❤️❤️
Great, thanks a lot its really very helpful, explained so nicely and in layman which is easily understanable
Excellent information about xlookup function thankyou very much sir.
Gr8, very good video explaining various uses of xlookup 👍👍
Waou !! Amazing presentation. Thank you for your great explanation.
Thank you very much.
How to search in a range of Data 2 columns but the return range is only 1 column ?
Very well explained. Nice
Great learning.. Thanks
Great video. Thank you.
Thank you so much for sharing
You are very great explainer in ever Excel videos
Thank you for sharing this! I have a "value" error problem, but I dont know how to fix. Can you advise? Thanks
Thanks, Very helpful
Awesome. Hands up. Keep it up
Brilliant video Sumit
Very helpful video sir thank u
thanks, helped a lot
Superb presentation .......
Excellent job sir
Thank you for your efforts
Glad you found it useful!
Great video buddy
Really great tutorial thanks
Glad you liked it Brain!
Very useful bro keep it up
Very useful information
Great examples! Thank you :-)
Thank you 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
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 :)
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
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.
many many thanks
Very good !
Hi Sumit, it's really amazing. I just have a query - Can we get value applying multiple criterion, as we do in Indexmatch!
Thank you master guru
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
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, 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!
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
Superb!
Can u create video using same formula for duplicate values.? How can we find output if lookup value has duplicates?
Very Interesting
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).
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.
What about if there are more than one highest scorer with same mark in one subject?
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.
Superb
Great video - I'm guessing those thumbs down videos are other Excel RUclips posters...?
Super sir
Hi, can u please make a video on how to fetch student's name who scored more than 99.. @22:33
Thanks
What if the label are different sequence ? how to use it?
Nice Job Sumit . Please Is it possible to return a data in Excel base on Selection
Sir, can we use this in Google sheets?
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.
How ahout multiple look-up cakes???. Could you please illustrate that with an example.
What if you want "Not appear" for the dual xlookup?
Amazing.
Glad you found the video useful!
Hi very nice video, however I am unable to download the file used as example
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???
.
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 :)
nice sir ji.
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’?)