The Major Fundamentals About Xlookup In Excel Along With Practical Examples

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])

Having the briefing done on the syntax, now, go ahead and try implementing XLookUp in Excel in a practical way.

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

How to Implement XLookUp?

You must consider an example of employee data. In this example, you have the following columns.

• Employee name
• Employee ID
• Department
• Salary
• 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.

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

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.

Next Steps

"Pivot Tables in Excel" can be your next stop. Pivot tables are one of the most important concepts for data analysis.

Become a Data Visualization Pro

Tableau Desktop Specialist Certification TrainingExplore Program

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!