How to Use the NEW & IMPROVED Excel XLOOKUP (with 5 Examples)

28 November 2019 Excel for Office 365 & Dynamic Arrays‍‍‍‍
‍
Join 400,000+ professionals in our courses: https://www.xelplus.com/courses/

Excel XLOOKUP will soon become the ONLY Excel LOOKUP Function you’ll ever need to solve any lookup problem you come across. If you'd like to learn about the other brand new Excel functions like FILTER & UNIQUE check out the complete course: https://courses.xelplus.com/p/excel-dynamic-array-formulas

In this video we cover 5 common Excel lookup problems:

1. Lookup returns an error (#NA) - This is a common problem with VLOOKUP. If the VLOOKUP function returns #NA it is likely because the item you're looking up does not exist on the lookup table. The solution so far has been to wrap the VLOOKUP function inside IFNA or IFERROR functions. With XLOOKUP however, you no longer need to do that. You just need to use the 4th argument of XLOOKUP [If_not_found] optional argument and specify the value you'd like to get back if the value does not exist on the lookup array.

2. Horizontal lookup - In legacy Excel when we needed to lookup a value in a table that was structured horizontally instead of vertically we would use the HLOOKUP function. The new XLOOkUP function can do horizontal lookups as well. You just have to select the horizontal array instead of the vertical array.

3. Look for a partial match instead of an exact match in Excel: There might be times you need to look for a partial match. In this case you can combine wildcards such as the asterisk or question mark characters and combine them together with the 5th argument of XLOOKUP which is the optional [match mode] argument.

4. Two way Excel lookup: To look up a value on the rows as well as columns we generally combined VLOOKUP with Excel Match function or used the famous INDEX & MATCH functions. We no longer need to use two separate functions to do a two way lookup. We can use XLOOKUP inside another XLOOKUP to get the job done. The advantage is we just need to know one function well, instead of combining different Excel lookup functions with one another.

5. Excel approximate match lookup: There are times you'd like to look for a value inside a data table and return the closest match. This is specially used for cases where you have thresholds for example for bonus schemes. If a number falls between a specific threshold you'd like to return a corresponding value. This is where you can use XLOOKUP's 5th argument [match mode] to look for "exact match or next smaller item" or "exact match or next larger item".

00:00 What is Excel's New XLOOKUP Function
01:15 Lookup Value Not Found with XLOOKUP
04:06 Horizontal Lookup with XLOOKUP in Excel
05:44 Look For Partial Match with XLOOKUP
07:51 Two-Way Lookup with XLOOKUP
10:29 Approximate Match with XLOOKUP

*** AVAILABILITY *** The XLOOKUP function is available to Office 365 and 2021 users.

⯆ DOWNLOAD the workbook here: https://www.xelplus.com/excel-xlookup-5-examples

LINKS to related videos - First XLOOKUP video: https://rumble.com/v2uqp14
Excel left lookup with XLOOKUP: https://rumble.com/v2us6fy

★ My Online Excel Courses ► https://www.xelplus.com/courses/

✉ Subscribe & get my TOP 10 Excel formulas e-book for free
https://www.xelplus.com/free-ebook/

RESOURCES I Recommend: https://www.xelplus.com/resources/

Get Office 365: https://microsoft.msafflnk.net/15OEg
Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593

GEAR
Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161
Main Camera: https://amzn.to/3a5ldBs
Backup Camera: https://amzn.to/2FLiFho
Main Lens: https://amzn.to/39apgeD
Zoom Lens: https://amzn.to/3bd5pN7
Audio Recorder: https://amzn.to/2Uo5rLm
Microphone: https://amzn.to/2xYy9em
Lights: http://amzn.to/2eJKg1U

More resources on my Amazon page: https://www.amazon.com/shop/leilagharani

Let’s connect on social:
Instagram: https://www.instagram.com/lgharani
Twitter: https://twitter.com/leilagharani
LinkedIn: https://at.linkedin.com/in/leilagharani

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#MsExcel #Excel #LeilaGharani