In this video, I will show you what is the new XLOOKUP function and 10 XLOOKUP examples.
Get Office 365 https://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: http://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 twoway lookup (i.e, look for a value that meets two lookup criteria)
4. ERROR HANDLING IN XLOOKUP: Error handling in inbuilt 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 multilevel 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 Function with Mul...
Free Excel Course https://trumpexcel.com/learnexcel/
Paid Online Training https://trumpexcel.com/exceltraining/
Best Excel Books: https://trumpexcel.com/bestexcelbooks/
⚙ Gear I Recommend:
Camera https://amzn.to/3bmHko7
Screen Recorder techsmith.z6rjha.net/26D9Q
USB Mic https://amzn.to/2uzhVHd
Wireless Mic: https://amzn.to/3blQ8uk
Lighting https://amzn.to/2uxOxRv
Subscribe to get awesome Excel Tips every week: https://www.youtube.com/user/trumpexc...
Note: Some of these links here are affiliate links!
#Excel #ExcelTips #ExcelTutorial