# Excel’s XLOOKUP

# 5 Examples

By now, you may have read about Excel’s new super lookup function called XLOOKUP.

XLOOKUP has the promise of becoming the only lookup function you’ll ever need for virtually any lookup problem you encounter. This will be a welcomed addition to Excel for those that struggle with VLOOKUP/HLOOKUP are just completely give up all hope when working with INDEX/MATCH functions.

Since its debut a few months ago, some enhancements to the function have been made that make it bigger and better than ever.

This post will examine some of the newest enhancements to XLOOKUP as well as explore some additional uses that were not covered in the previous XLOOKUP post a few months earlier.

# Handling Items Not Found

In our data sheet (*named “Salary”*), we have a list of employee names and their yearly salaries.

Our mission is to calculate the maximum bonus each employee can achieve.

On a separate tab (*named “B_Master”*), we have a list of employee IDs, their bonus percentage, and the employee names.

We aren’t working with a tiered bonus scheme; each employee can have their own bonus percentage.

We want to bring the bonus percentages located on the “B_Master” sheet and place them in **Column C** next to their respective employees on the “Salary” sheet.

Since we don’t have the IDs on the “Salary” sheet, we’ll create a lookup formula that uses the employee names.

On the “Salary” sheet, select cell **C3** and enter the following formula:

=XLOOKUP(A3, B_Master!$C$4:$C$28, B_Master!$B$4:$B$28)

*NOTE: The default match mode for XLOOKUP is to perform an exact match, unlike VLOOKUP’s approximate match default mode.*

Fill the formula down the adjacent rows to repeat the formula for each employee.

Notice that the XLOOKUP function returned #N/A error for any employee name that was not found on the “B_Master” sheet.

To gracefully handle these errors, we will use the 4^{th}, optional argument named **[if_not_found]** to reprogram the default error message with something more mathematically-friendly.

=XLOOKUP(A3, B_Master!$C$4:$C$28, B_Master!$B$4:$B$28, 0)

The unknown users are given a bonus percentage of 0 (*zero*).

Because we want to calculate the bonus amount, we will revise the formula to multiply the returned bonus percentage by the yearly salary.

=XLOOKUP(A3, B_Master!$C$4:$C$28, B_Master!$B$4:$B$28, 0) * B3

Fill the formula down the adjacent rows to update the formula for each employee.

# Horizontal Lookup

What would we do if the table were arranged left-to-right?

In the “old days” we would perform a horizontal lookup with the **HLOOKUP** function instead of a vertical lookup using the **VLOOKUP** function.

XLOOKUP doesn’t care about a table’s orientation.

Let’s perform the same task are the previous example, calculating a bonus amount, but this time we will find the employee name on **Row 5** and the percentage on **Row 4** of the “B_Master” sheet.

On the “Salary” sheet, select cell **D3** and enter the following formula:

=XLOOKUP(A3, B_Master!$F$5:$AD$5, B_Master!$F$4:$AD$4, 0) * B3

Fill the formula down the adjacent rows to repeat the formula for each employee.

*SPECIAL NOTE: In both examples, the range of names being searched was located AFTER the range of percentages being returned. This would be impossible with a traditional VLOOKUP or HLOOKUP function.*

# Looking for a Partial Match

# (*wildcards*)

Suppose we only remember the last name of our employee; in this case, “Willard”. We want to discover the full name of the employee.

We’ll begin by placing the employee’s last name in cell **F3**, then select cell **G3** and enter the following formula.

=XLOOKUP(“*” & F3, A3:A20, A3:A20, “Not Found”)

Because the wildcard character “*” (*asterisk*) is a text character, we must enclose the asterisk within a set of double-quotes prior to concatenating the character to the **F3** cell reference.

The lookup fails because we are not using the correct match mode.

Remember, the default match mode is to perform an exact match. We need to select the optional argument **[match_mode]** and set it to option **2 – Wildcard character match**.

The revised formula will appear as follows.

=XLOOKUP(“*” & F3, A3:A20, A3:A20, “Not Found”, 2)

If you don’t wish to use the **[if_not_found]** argument, you will need to account for the argument position by placing an extra comma after the **return_array** argument.

=XLOOKUP(“*” & F3, A3:A20, A3:A20, , 2)

# Two-Way Lookup

For our example, we have the following table.

A two-way lookup operates by first searching for an item within a column of data.

Next, a second search is performed to locate an item across a row of data.

Once these two pieces of information are located, the intersection of the discovered row and column is derived, and the data contained at that intersection is returned.

To make user selections easier, we will create two Data Validation lists: one for the Employee (** =$A$3:$A$20**) and one for the adjacent column’s headers (

**).**

*=$B$2:$D$2*Select cell **F3**, then click **Data (tab) -> Data Tools (group) -> Data Validation**.

In the Data Validation dialog box, set the **Allow** dropdown to **List** and the **Source** field to **=$A$3:$A$20**.

Select cell **F4**, then click **Data (tab) -> Data Tools (group) -> Data Validation**.

In the Data Validation dialog box, set the **Allow** dropdown to **List** and the **Source** field to **=$B$2:$D$2**.

Select an employee from the dropdown in cell **F3** and a category from the dropdown in cell **F4**.

Our goal is to see the value associated with the selected employee and the selected category.

Select cell **F5** and enter the following formula.

=XLOOKUP(F3, A3:A20, XLOOKUP(F4, B2:D2, B3:D20) )

Test the formula by selecting various combinations of employees (**F3**) and categories (**F4**).

# Approximate Match Lookup

Continuing with our previous example, suppose the company has received a new bonus scheme.

Anyone earning up to just under 30,000 does not receive a bonus. From 30,000 to just under 50,000, the employee earns a 5% bonus on their yearly salary. From 50,000 to just under 60,000 they receive an 8% bonus, and so on.

Although we wish to calculate the bonus amount, we first need to determine the applicable bonus percentage. Once the bonus percentage is determined, we will multiply the percentage against the yearly salary to arrive at the bonus amount.

On the “Salary” sheet, select cell **E3** and enter the following formula.

=XLOOKUP(B3, B_Master!$E$13:$E$17, B_Master!$F$13:$F$17, , -1)

*NOTE: We are skipping the [if_not_found] argument, so we need to make sure we place 2 commas to account for the unused argument.*

Because XLOOKUP defaults to “exact match” mode, we are using the “-1” option for the **[match_mode]** argument. If we fail to locate an exact match of the salary, we want to return the next closest bonus that doesn’t exceed the salary.

The **[match_mode]** arguments are as follows:

We see that Gary Miller, with a yearly salary of 60,270, receives a bonus percentage of 10%.

60,000 is the closet salary to Gary’s 60,270 without going over.

Fill the formula down to the remaining employees and we see the following results.

Because we wanted to calculate the bonus amount, not find the bonus percentage, we will return to cell **E3** and modify the formula to multiply the discovered bonus percentage by the employee’s yearly salary.

=XLOOKUP(B3, B_Master!$E$13:$E$17, $F$13:$F$17, , -1) * B3

Fill the formula down the column to replicate the updated formula.

## Practice Workbook

Feel free to Download the Workbook HERE.

zhongxinliDecember 27, 2019 at 8:57 amThank you the editor teacher for so much specific contents for us to learn from you , very professional and wonderful , stimulating our daily mind