Category:

Course Description:

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”. Course Length: 13:33 minutes Source: Leila Gharani

Thousands of courses

DisclaimerMany of the curated courses have been manually added into our Learning Management System, then enhanced with structure, descriptive, activities and consolidation,  referencing who the content creators are (where required). If you like the curated content from any creator and like their materials, please feel free to connect with them directly to explore their full libraries of paid programs. 

We will continue to add more curated quality content as well as increasing partnerships with creators to provide you with an all-in-one skills development platform. 

Book a demo now!

Tell us a bit about you. After review, you will receive an invite calendar where you can select your demo time.