What is XLOOKUP? • XLOOKUP is a function which 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 • 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
Email: Support@skillfinlearning.com
Mobile: 9650010925
XLOOKUP Syntex =XLOOKUP(Lookup_value, lookup_array, return_array, [if_not_found], [match,mode], [search_mode]) The first 3 inputs are compulsory, while the other three are optional. Match_mode – Here the user has to specify how to match the lookup value against values in lookup array. Default value is 0 which means exact match. Other options available are; • -1 which means exact match or next smaller one • 1, means exact match or next larger item • 2, means wild card character match
Lookup_value – It is the value which we want to search for Lookup_array – It is the array or range where we want to search
Return_array – It is the array or range we want to be returned by the XLOOKUP function
Email: Support@skillfinlearning.com
If_not_found – It is the value to return if the Lookup value is not found.
Search_mode – Here the user has to specify the search mode to use. By default a first to last search mode is used in the formula. • 1 is search first to last • -1 is search last to first • 2 is Binary search (sorted ascending order) • -2 is Binary search (sorted descending order)
Mobile: 9650010925
Example 1 - Comparison with VLOOKUP (1/4) We have a list of employees with their Employee ID and annual salary in the database.
We want to pull the Employee name and annual salary in our excel model.
How can we do it?
Email: Support@skillfinlearning.com
Mobile: 9650010925
Example 1 Comparison with VLOOKUP (2/4) We can use either a VLOOKUP function or XLOOKUP.
Go to Formulas, Select Lookup & Reference and then Select XLOOKUP.
Let us discuss how to use XLOOKUP function. • For XLOOKUP, we will here use just the first 3 variables (Ie; Lookup_value, Lookup_array and return_array). • We want to pull the data using the employee ID. Where if we enter the employee ID, we get the employee name and his annual salary.
Email: Support@skillfinlearning.com
Mobile: 9650010925
Example 1 Comparison with VLOOKUP (3/4) XLOOKUP FUNCTION ARGUMENTS Lookup_value is the Employee ID. Link it to “Cell I8”
` ` Lookup_array is the Range (B6:B17)
Return_array is the Range (C6:C17)
• • •
Similarly, for annual salary, the first two selections are the same. For the Return_array, select the range (E6:E17). Then press OK. You get the following output.
Leave other function arguments blank and then press OK.
Email: Support@skillfinlearning.com
Mobile: 9650010925
Example 1 Comparison with VLOOKUP (4/4) The advantage of XLOOKUP over VLOOKUP is that on inserting new columns, XLOOKUP keeps working while VLOOKUP loses the output. •
Below Figure shows, output from XLOOKUP and VLOOKUP formula’s
•
When we insert a column in before column E, this is what happens to the two formulas.
•
•
Email: Support@skillfinlearning.com
While the annual salary calculated using VLOOKUP function stops working, the new XLOOKUP function is not impacted by the inserted new column. So, it is advantageous to use XLOOKUP over VLOOKUP function.
Mobile: 9650010925
Example 2 - Comparison with HLOOKUP (1/2) We have the same data produced horizontally. We want to pull the annual salary for employees with their EMP ID (as done in the previous example).
Lookup_array is the Range (C5:N5) Lookup_value is the Employee ID. Link it to “Cell B11”
Return_array is the Range (C7:N7) Leave other function arguments blank and then press OK.
Email: Support@skillfinlearning.com
Mobile: 9650010925
Example 2 Comparison with HLOOKUP (2/2) The advantage of XLOOKUP over HLOOKUP is that on inserting new rows, XLOOKUP keeps working while HLOOKUP breaks. Below Figure shows, output from XLOOKUP and HLOOKUP formula’s
• •
When we insert a row in before row 6, this is what happens to the two formulas.
Just as in the case of VLOOKUP, the annual salary calculated using HLOOKUP function stops working, while the new XLOOKUP function is not impacted by the inserted new row. So, it is advantageous to use XLOOKUP over HLOOKUP function.
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 Visit Skillfinlearning.com
Email: Support@skillfinlearning.com
Mobile: 9650010925