XLookUp in Excel is the most advanced version of lookup functions. Older versions of Excel had horizontal and vertical lookup functions only. The latest version of Excel has a more optimized and hybrid lookup function. You will look into everything about the XLookup in this tutorial.
What Is XLookUp?
XLookUp in Excel is an exploratory function. XLookUp in Excel is used to search for an element in a range of elements row-wise. Users can fetch the element in one column for a target element and return a result element from the same row in another column, regardless of which side the return column is on.
Now, you will look at the syntax of how to use XLookUp in real-time.
Syntax Of XLookUp
The XLookUp in Excel function initiates the search for the element in a range or an array and returns the item corresponding to the first match it finds. If no match is found, the XLookUp function returns the closest or approximate match.
The syntax of XLookUp in Excel is as shown below.
=XLOOKUP(lookup value, lookup array range, return array, [if element not found], [element match mode], [element search mode])
Syntax Term Description
The element you are searching for. This field is mandatory.
lookup array range
The range of elements to search. This field is mandatory.
The array or range to return. This field is mandatory.
[if element not found]
Exception message when an element is not found. Not mandatory.
[element match mode]
Specifying Match type
Specifying Match type
0 - Exact match. If none are found, return #N/A. This is the default.
-1 - Exact match. If none are found, return the next smaller item.
1 - Exact match. If none are found, return the next larger item.
2 - A wildcard match where *, ?, and ~ have special meaning.
[element search mode]
Specifying the search mode to implement:
1 - Perform a search starting at the first item. This is the default.
-1 - Perform a reverse search starting at the last item.
2 - Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned.
-2 - Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.
Having the briefing done on the syntax, now, go ahead and try implementing XLookUp in Excel in a practical way.
How to Implement XLookUp?
You must consider an example of employee data. In this example, you have the following columns.
- Employee name
- Employee ID
- Date of Joining
Along with the data, you also have a secondary table storing the bonus percentage of employees based on salary.
Now, imagine, over time; a few changes have occurred in the organization leading to an increase in employee salary and promotion. The problem statement would be to find out the original designation and the current/latest designation/department of a given employee and the current salary/bonus percentage.
Now, fetch each data one by one.
Firstly, fetch the original department of a given employee. The XLookUp formula for the solution will be as follows.
=XLOOKUP(‘Emp DataRevised’!A2, Table1[Name], Table1[Department])
Here, 'Emp DataRevised'!A2 is the lookup value. Table1[Name], Table1[Department] are the array ranges to fetch the data.
You will have the first answer as admin for an employee named 'Mary' as shown below.
Next, you can drag the resultant cell to all the remaining rows to copy the formula and obtain the respective results. The resultant table will be as follows.
Next, you must try to fetch the current department. The formula will be completely similar, with a minor tweak in the last step. The element search mode. By default, Excel will consider the first to last option. You must manually update it as last to first to fetch the most recent data as per your data.
Now the formula will slightly change, as shown below.
=XLOOKUP(‘Emp DataRevised’!A2, Table1[Name], Table1[Department], , 0, -1)
Here, the last section of the formula written as -1 depicts the 'Last to First' feature.
The resultant data is shown in the image below.
Finally, go ahead and try to fetch the salary and bonus details of the employees based on their current salary.
Now, to find the bonus percentage, the lookup value will be the current salary values of the employees. So, accordingly, your formula will change as follows.
=XLOOKUP(E2, Table2[Salary], Table2[Bonus], , , -1)
Here, you have chosen -1 for the element match mode. -1 describes the exact or smallest near value. The final answer is as shown below.
With that, you reached the end of this informational and insightful XLookUp in Excel tutorial.
Looking forward to a career as a Business Analyst? Check out the Business Analytics Certification Training with Excel and get certified today.
This Business Analytics certification course offered by Simplilearn teaches you the basic concepts of data analysis and statistics to help data-driven decision-making. This training introduces you to Power BI and delves into the statistical concepts that will help you devise insights from available data to present your findings using executive-level dashboards.
If you have any questions about this tutorial’s topic, please feel free to leave them in the comments section below. Our 24/ expert team will answer all your queries for you at the earliest!