Understanding XLOOKUP with Examples (Part 2)
$
$
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 presentation, we discussed the advantages of using XLOOKUP and compared it vs VLOOKUP and HLOOKUP. • This document focusses on the 3rd and 4th advantage (highlighted above) of using XLOOKUP Email: Support@skillfinlearning.com
Mobile: 9650010925
Example 1 - PULLS VALUE BOTH FROM THE RIGHT AND LEFT OF A LOOKUP RANGE (1/4) We have a list of employees with their Employee ID and annual salary in the database.
We want to pull the Employee ID (Left of lookup) and annual salary (right of look up) in our excel model.
Lookup value
How can we do it?
Email: Support@skillfinlearning.com
Mobile: 9650010925
Example 1 - PULLS VALUE BOTH FROM THE RIGHT AND LEFT OF A LOOKUP RANGE (2/4) Go to Formulas, Select Lookup & Reference and then Select XLOOKUP. • We can only pull the Annual salary using VLOOKUP function (since it is on the right of the Lookup value which is the Employee Name) and not the Employee ID (since it is towards the left of the look up value). • However, we can use XLOOKUP to pull both the data sets in our excel model. • For XLOOKUP, we will here use just the first 3 variables (Ie; Lookup_value, Lookup_array and return_array).
Email: Support@skillfinlearning.com
Mobile: 9650010925
Example 1 - PULLS VALUE BOTH FROM THE RIGHT AND LEFT OF A LOOKUP RANGE (3/4) XLOOKUP FUNCTION ARGUMENTS Lookup_value is the Employee name. Link it to “Cell G8”
• • •
` `
Lookup_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.
Return_array is the Range (B6:B17)
Leave other function arguments blank and then press OK.
Email: Support@skillfinlearning.com
Mobile: 9650010925
Example 1 - PULLS VALUE BOTH FROM THE RIGHT AND LEFT OF A LOOKUP RANGE (4/4)
•
The advantage of using XLOOKUP is that it can pull data both from the left and right columns of the lookup, which was not possible with the VLOOKUP function.
•
Similarly, XLOOKUP can pull data from rows above and below the LOOKUP array which could not be done using HLOOKUP function.
•
Want to try by transposing the same example in another excel sheet.
•
Please reach out at support@skillfinlearning.com in case you find any difficulty.
Email: Support@skillfinlearning.com
Mobile: 9650010925
Example 2 - FINDS BOTH THE FIRST AND LAST OCCURANCE OF A VALUE (1/3) Here is the data showing Customer ID, Name, Date of sale and Amount ($). Name of a few customers is repetitive (Eg. John Doe, Imam W etc.) We want to pull the date of last sale and the Sale amount for a few customers.
While Smith Jones is appearing just once, Jackie and John are appearing multiple times in the original data.
Email: Support@skillfinlearning.com
Mobile: 9650010925
Example 2 - FINDS BOTH THE FIRST AND LAST OCCURANCE OF A VALUE (2/3) The default value pulled by the XLOOKUP function is the first occurrence, but we have the option to select the Last occurrence when we have multiple entries for the same lookup value. Here is how we can do it.
XLOOKUP FUNCTION ARGUMENTS Lookup_value is the Customer Name. Link it to “Cell G8”
Return_array is the Range ($C$6:$C$21), ie; Date of sale
` `
Lookup_array is the Range ($D$6:$D$21) , ie; Customer name Match_mode – Leave blank
If_not_found – Leave blank
Search_mode - Type -1, as we are finding the last value in the lookup range.
Then Press OK
Email: Support@skillfinlearning.com
Mobile: 9650010925
Example 2 - FINDS BOTH THE FIRST AND LAST OCCURANCE OF A VALUE (3/3)
• • •
Fix the Lookup array and return array using F4 key. Copy and paste the formula in remaining two rows. Similarly work on the XLOOKUP function for the last sale. First two function arguments are the same. For Return array select range ($E$6:$E$21), ie, Sale Amount. Search_Mode - Type -1 as we are finding the last value in the lookup range. Then Press OK.
` `
• •
Here is the output you get •
Email: Support@skillfinlearning.com
For Smith Jones, we get the Date of Sale as 14 Jan 2020, which matches with the one record present for the customer. For the other two customers Jackie Deol and John Doe, we get the date of sale as 14th May 2020 and 2nd April 2019, which is the last entry in the data range for these customers. This output was not feasible with any of the other lookup functions, like VLOOKUP, HLOOKUP, INDEX, MATCH or OFFSET function earlier.
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