In this video, I will show you what is the new XLOOKUP function and 10 XLOOKUP examples.
Get Office 365 –
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:
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 with Multiple Criteria –
Free Excel Course –
Paid Online Training –
Best Excel Books:
⚙️ Gear I Recommend:
Camera –
Screen Recorder – techsmith.z6rjha.net/26D9Q
USB Mic –
Wireless Mic:
Lighting –
Subscribe to get awesome Excel Tips every week:
Note: Some of these links here are affiliate links!
#Excel #ExcelTips #ExcelTutorial
Watch more new videos about Excel Office | Synthesized by Mindovermetal English
Great video. Thank you.
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?
This was great. Thank you so much!
Very helpful video sir thank u
Super sir
13:50 You must explain why you are locking the values because a beginner would get confused
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.
Great video buddy
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?
Thank you so much for sharing
What if the label are different sequence ? how to use it?
Good Teacher
You did not explain match and index
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.
Excel 2016 I don't see XLookup. Only Vlookup.
Thanks so much for the tutorial…
its very helpful
I am not able to use the 2nd step of lookup entire data, I am using 2016 version
In xlookup how to search 1st, 2nd & 3rd highest price reference to a ite
How ahout multiple look-up cakes???. Could you please illustrate that with an example.
Excellent video, unfortunately, I cannot download the excel file.
What about if there are more than one highest scorer with same mark in one subject?
Thank you!!!!
How to use this formule on numbers? Criteria for numbers
I liked the video. It was very educational but the ads were too much.