Understanding XLOOKUP with Examples (Part 3)
$
$
FOUNDER
What is XLOOKUP?
• XLOOKUP is newly introduced function in MS Office 365 • It can be used in place of - VLOOKUP - HLOOKUP - Index and Match
• It is currently not available in older versions of MS Excel
Email: Support@skillfinlearning.com
Mobile: 9650010925
Advantages of using XLOOKUP
Focus of this document
• Lookups both vertical and horizontal ranges • XLOOKUP does not break when we insert new rows and columns to the data set • XLOOKUP can help you get the values both from the left as well as the right of the lookup range (better than VLOOKUP). It can help you get values from the Cells above as well as below the lookup range (better than HLOOKUP). • It can help you find the first as well as last occurrence of a value. • It does an exact match by default but can also find the nearest number if the exact match is not found • It also helps in error handling in case the lookup value is not present in the data source • It can return a range of cells or a single cell (as done by INDEX function) • It also supports wildcards for partial matches • In our last 2 presentations, we discussed the first 4 advantages of using XLOOKUP function. • This document focusses on the 5th and 6th advantage (highlighted above) of using XLOOKUP Email: Support@skillfinlearning.com
Mobile: 9650010925
Example 1 - FINDS a nearest match if exact match is not found (1/5) We have a data set showing Employee ID and their Annual salary. We also have the % bonus payable for different salary ranges. We have to calculate the annual bonus for each employee in column D. Can we do it with one formula?
Yes, it is possible now with X-LOOKUP.
Email: Support@skillfinlearning.com
Mobile: 9650010925
Example 1 - FINDS a nearest match if exact match is not found (2/5) Go to Formulas, Select Lookup & Reference and then Select XLOOKUP.
Lookup_value is the Employee name. Link it to “Cell C6�
` `
Lookup_array is the Salary Range in the second table (F6:F13)
Return_array is the Bonus Percent Range (G6:G13)
Leave other function arguments blank and then press OK.
Email: Support@skillfinlearning.com
Mobile: 9650010925
Example 1 - FINDS a nearest match if exact match is not found (3/5) You get the following error. • • •
•
Here, we will have to put an extra argument, as we do not have the exact match for salaries. In Match mode enter -1 (instead of leaving it blank or entering 0). -1 means next smaller value if exact match is not found.
This is because XLOOKUP matches exact value by default. We do not have the exact salary ranges for bonus calculations.
Email: Support@skillfinlearning.com
Mobile: 9650010925
Example 1 - FINDS a nearest match if exact match is not found (3/5) •
•
XLOOKUP takes the next lower salary range if exact match is not found. For salary range of $30,000-49,999 the bonus is 10%. Now you get the following output.
• • •
Multiply the Bonus percent with Annual Salary to get the annual bonus. Then copy and paste the formula to other cells in the row. You get the following output
In case you want to enter the next larger value enter 1 (instead of -1).
Email: Support@skillfinlearning.com
Mobile: 9650010925
Example 1 - FINDS a nearest match if exact match is not found (5/5)
• XLOOKUP has made it easier for us to do complex calculations. • In the absence of this function, we would have to place a multiple conditional formula to calculate the bonus in the above example.
Email: Support@skillfinlearning.com
Mobile: 9650010925
Example 2 - HANDLES ERRORS IN CASE THE LOOKUP VALUE IS NOT PRESENT IN THE DATA SOURCE (1/3)
Here is the data set showing employee ID, Annual salary and Bonus percent. We want to pull the annual salary of few employees in our model.
While Emp 17005 is appearing in the list, Emp 17020 is not there in the list
Email: Support@skillfinlearning.com
Mobile: 9650010925
Example 1 - HANDLES ERRORS IN CASE THE LOOKUP VALUE IS NOT PRESENT IN THE DATA SOURCE (2/3)
Go to Formulas, Select Lookup & Reference and then Select XLOOKUP.
Lookup_value is the Employee ID. Link it to “Cell B21”
` `
Lookup_array is the Emp ID. Link to cells (B6:B17)
Return_array is the Salary Range in the table (C6:C17)
Leave other function arguments blank and then press OK.
Email: Support@skillfinlearning.com
Mobile: 9650010925
Example 1 - HANDLES ERRORS IN CASE THE LOOKUP VALUE IS NOT PRESENT IN THE DATA SOURCE (3/3)
This time we get the output as shown below.
If we use a VlOOKUP or index and match here, we will have to use an conditional formula that if the employee is not found put a zero. But it is much easier to put the same condition using a XLOOKUP function. • In this case XLOOKUP shows annual salary of the second employee as 0 as he is not in the original data set.
Email: Support@skillfinlearning.com
Mobile: 9650010925
Explore our Data Analysts Skills Training (DAST) in Excel Go from beginner to an Expert in Data analytics Skills in Excel. Join this program to build your Excel analytical skill quickly through hands on project Includes: Annual (1 year) access Take the program anytime anywhere Direct access to the instructor Weekly mentorship assistance 6.5 hours on-demand HD videos 20+ real life case illustrations 5 Practice assignment Access on Mobile and Tablet Certificate of Completion 30-day money back guarantee
Email: Support@skillfinlearning.com
You get a Personalized Training Experience: Unlimited access to video content 1 on 1 coaching from expert instructor Individual mentorship assistance Dedicated doubts resolution sessions Practice on real world projects from industry experts Evaluation and feedback on assignments For more details please visit; Data Analyst Skills Training in Excel (DAST)
Mobile: 9650010925
Explore our “Financial Analysts Skills Training” (FAST) course Master the complete financial analytics and Excel modeling skills to get trained as a Financial analyst by an Expert instructor Includes: Annual (1 year) access Take the program anytime anywhere Direct access to the instructor Weekly mentorship assistance 10 hours on-demand HD videos 7 real life case illustrations 7 Practice assignment Access on Mobile and Tablet Certificate of Completion 30-day money back guarantee
Email: Support@skillfinlearning.com
You get a Personalized Training Experience: Unlimited access to video content 1 on 1 coaching from expert instructor Individual mentorship assistance Dedicated doubts resolution sessions Practice on real world projects from industry experts Evaluation and feedback on assignments For more details please visit; Financial Analyst Skills Training (FAST)
Mobile: 9650010925
THANK YOU
Email: Support@skillfinlearning.com
Mobile: 9650010925
Visit us: www.skillfinlearning.com Chat with us: +91 96500 10925 Email us: support@skillfinlearning.com