Welcome! Lets start your excel journey..

Welcome! Register Now to Unlock Exclusive Excel Tutorials

Get Started
  • Exclusive Playlists
  • Downloadable Videos
  • No Ads!
  • Exclusive Templates

How to use XLOOKUP in Excel (7 Examples) - The Ultimate Guide

Admin
Duration: 16:30
Submitted: 7 months ago
Views: 374

Comments (0)

Link to this video:

Description:

If you're still using the VLOOKUP function in Excel, you should switch to XLOOKUP. The XLOOKUP function is newer and easier to use than VLOOKUP. It also has some additional advantages, such as the ability to lookup values in arrays.

We have also learn Closest Match in excel previously, so if you haven't yet following Lookup functions and formulas playlist , you should do the same to learn all these functions.

In this Video post, we'll show you how to use the XLOOKUP function and how it can help you get your work done more quickly and easily.

We will learn 7 examples for using XLOOKUP as you can see in timeline below;

Table Of Contents

00:00 Introduction

00:49 Xlookup Basics

03:34 Left Lookup With XLOOKUP

05:06 Horizontal Lookup (HLOOKUP) With XLOOKUP

06:19 NOT FOUND Error With XLOOKUP

07:55 Multiple Values Using XLOOKUP

09:40 Approximate Match With XLOOKUP

13:17 Last Match With XLOOKUP

16:00 Conclusion

Lets learn the syntax of XLOOKUP function as follows;

=XLOOKUP(lookup_value, lookup_array, return_array,[If Not Found],[Match Mode],[Search Mode])```

Where;

- lookup_value: the value that you want to use as a search key. For example if we look for an employee salary then this will be an employee name.

- lookup_array: the range of cells that contain values to search in and these must be arranged vertically (from top to bottom). For example if we look for an employee salary then this will be a column contains the list of employees names.

- return_array: the range of cells that contain the values to return when a match is found. For example if we look for an employee salary then this will be a column contains their salaries.

- If Not Found: what should happen if the lookup value isn't in your data? By default Excel shows us #N/A error; however, you can use "*" to return multiple values or you can use a custom string such as “Not Found”.

- Match Mode: This is the way that Excel will match your lookup values with those in the lookup array. By default, we have an exact match (0) but you can also select to do an approximate match (–). In this case, Excel will look for the closest match to your lookup value.

- Search Mode: This is the way that Excel looks through the values in your lookup array. By default, it starts at the top left cell (A$) and goes down column by column until it finds a match. If you want Excel to start searching from a different cell or go in a different direction, you can use the Search Direction and Starting Cell properties.

Now that we know the syntax of XLOOKUP function, let see some examples;

Left Lookup Using XLOOKUP Function

Vlookup's only limitation is that it cant look to the left. Therefore, we have xlookup function which can look anywhere. In

the below example, we will lookup active_flag in column A and return customer name from column B.

=XLOOKUP(A15,'Sheet Name'!A:B,C15)

Horizontal Lookup (HLOOKUP) With XLOOKUP

In this example, we want to lookup the employee name in row A and return their corresponding salary from column B.

=XLOOKUP("John",A:B,D$11)

This will return the value "6000" as John's salary is found in cell B$6000. If Not Found If John isn't found in the data, then Excel will return the value "Not Found" from cell C$11. Multiple Values Using XLOOKUP In this example, we want to lookup the employee name in column A and return their corresponding salary and department from column B.

=XLOOKUP("John",A:B,C:D)

This will return the value "6000;Accounting" as John's salary is found in cell C$6000 and his department is found in cell D$15. If Not Found If John isn't found in the data, then Excel will return the value "Not Found" from cell E$11.

Approximate Match With XLOOKUP

In this example, we want to lookup the employee name in column A and return their corresponding salary from column B using approximate match which means if there is no exact match, Excel will find the closest match.

=XLOOKUP("John",A:B,D$11)

This will return the value "6000" as John's salary is found in cell B$6000 which is the closest match to "John". If Not Found If John isn't found in the data, then Excel will return the value "Not Found" from cell C$11.

Replace NOT FOUND Error With XLOOKUP

In this example, we want to lookup the employee name in column A and return their corresponding salary from column B. If John isn't found in the data, then Excel will return his empty cell as a blank instead of an error value (#N/A).

=XLOOKUP("John",A:B,C:D,”Not Found”)

This will return the value "6000" as John's salary is found in cell C$6000. If Not Found If John isn't found in the data, then Excel will return a blank instead of an error.

Multiple Values Using XLOOKUP Function

In this example, we will look for multiple values using xlookup function in excel.

Follow along the video to know how we can utilise the Xlookup function to look around multiple values in excel.

XLOOKUP is such a versatile function that you can use it however you want and its the best thing happened to excel users who either uses Vlookup or INDEX MATCH function in excel.

***

Find Last Match With XLOOKUP Function

The XLOOKUP function is similar to the VLOOKUP function, but there are some key differences. For example, the XLOOKUP function can find the last match in a table, while the VLOOKUP function can only find the first match. This can be very useful when you have a large table of data and need to look up data based on the last row.

If you have Excel 365 or Excel 2021, use XLOOKUP instead of VLOOKUP to find the last match with XLOOKUP function in excel. The XLOOKUP function is easier to use and has some additional advantages.

Conclusion

The XLOOKUP function is a great way to find the last match in a table of data. It is easier to use than the VLOOKUP function, and it has some additional advantages. If you have Excel 365 or Excel 2021, be sure to use the XLOOKUP function instead of the VLOOKUP function.