Download the XLOOKUP Practice Kit
Master Excel’s most powerful lookup function. Get the exact workbook from the video with all 5 advanced examples — including two-way lookups and wildcard matches.
Syntax
When using XLOOKUP, you just provide 3 required parameters (and 3 more optional parameters) and Excel does the rest.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])- lookup_value: The value you are looking for
- looup_array: The list to search for this value
- return_array: The list where you want the result from
- [if_not_found]: If no match is found, return the supplied [if_not_found] text (optional)
- [match_mode]: the match type to perform (optional)
- [search_mode]: the direction of search (optional)
For an introduction to XLOOKUP in Excel, check out our article here.
Custom Message If 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 with XLOOKUP:
=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 4th, 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.
XLOOKUP with Wildcards (Partial Match)
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 (Nested XLOOKUP)
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 (the closest one)
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)
💡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 closest one that doesn’t exceed the salary.
The Match Mode arguments [match_mode] 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
Get the XLOOKUP Practice Kit:
Includes all 5 advanced examples and the final solution workbook.
Featured Course
Master Excel’s Essential Modern Functions
Leila Gharani
Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.
