How to use XLOOKUP in Excel – The Ultimate Guide

In this tutorial, we’ll be covering the basics of using the XLOOKUP function in Excel. This function is similar to the VLOOKUP function but has some additional capabilities that make it even more powerful. We’ll be walking you through the process step-by-step, so even if you’re new to Excel, you’ll be able to follow along.

Basics of XLOOKUP

Let’s focus on the basics of using the XLOOKUP function first. We’ll be using a simple example to demonstrate how the function works. Let’s do this by looking up a last name based on a row number with the XLOOKUP function.

Step 1: Understand the Arguments of the XLOOKUP Function

You’ll need to know its arguments to understand how the XLOOKUP function works. There are three main arguments: the lookup value, the lookup array, and the return array. There are also three optional arguments which we will ignore for now. The formula for this function is

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Step 2: Enter the XLOOKUP Function in the Worksheet

Once you understand the arguments of the XLOOKUP function, you can enter them into the worksheet. To do this, simply start typing “=XLOOKUP” in the cell where you want the result to appear, and Excel will automatically show you the function’s arguments.

Step 3: Enter the Lookup Value and Lookup Array

In our example, the lookup value will be the individual rolls, the lookup array will be the row numbers (roll numbers), and the return array will be the last names. The formula will become =XLOOKUP($I$5,$A$4:$A$16,$C$4:$C$16)

Make sure to make the references absolute by selecting and using the F4 key. This will prevent the data from moving around and ensure the formula always references the correct cells.

Step 4: Close the Parenthesis and Check the Result

Once you’ve entered all the arguments, close the parenthesis and press Enter. The result of the XLOOKUP function should appear in the cell where you entered the formula. In our example, it should return the last name corresponding to the roll we entered.

Left Lookup Using XLOOKUP Function

Traditionally VLOOKUP looks to the vertical and the right, and that’s its only limitation is it doesn’t look to the left. To resolve that, we can use the XLOOKUP function. Here is how to do that.

Step 1: Enter the XLOOKUP Function in the Worksheet

Once you understand the left lookup function, you can enter it into the worksheet. To do this, simply start typing “=XLOOKUP” in the cell where you want the result to appear, and Excel will automatically show you the function’s arguments.

Step 2: Enter the Lookup Value and Lookup Array

In the first argument of the XLOOKUP function, enter the lookup value (in our example, the last name is the lookup value). Enter the lookup array (the last name column) in the second. Enter the return array (the first name column) in the third argument. Make sure to make the references absolute by using the F4 key. The formula will =XLOOKUP($I$6,$C$4:$C$16,$B$4:$B$16)

Step 3: Close the Parenthesis and Check the Result

Once you’ve entered all the arguments, close the parenthesis and press Enter. You should get the result after that.

Multiple Values Using XLOOKUP Function

In this part of the tutorial, we’ll cover how to use the XLOOKUP function to retrieve multiple values simultaneously. It can be done when the lookup value headers are the same. This can be useful when you have a table with multiple columns of data that you want to retrieve based on a single lookup value.

Step 1: Identify the Values

The first step in using the XLOOKUP function to retrieve multiple values is to identify the lookup value and lookup array. In our example, the lookup value is row number A2 (a specific roll), and the lookup array is the range of roll number (1 to 13).

You’ll also need to identify the return array. In our example, the return array is the range of the data in the three columns next to the lookup value (B4:D16).

Step 2: Enter the XLOOKUP Function in the Worksheet

Once you’ve identified the lookup value, lookup array, and return an array, you can enter the XLOOKUP function in the worksheet. To do this, start by typing “=XLOOKUP” in the cell. Then, enter the lookup value (A2) and lookup array (A4:A16). In the third argument, enter the return array (B4:D16).The formula will look like this in our case: =XLOOKUP(A2,A4:A16,B4:D16).

Step 3: Close the Parenthesis and Check the Result

Once you’ve entered all the arguments, close the parenthesis and press Enter. The result of the XLOOKUP function should appear in the cell where you entered the formula.

Conclusion

The XLOOKUP function is a powerful and versatile tool that can help you find values quickly in Excel. As you can see from this tutorial, you can utilize it in many ways by combining it with other functions.

We hope you found this tutorial helpful and that you better understand how to use the XLOOKUP function in your Excel worksheets. Check out our video on the same topic, which shows several examples and the use of the function in action.