Excel XLOOKUP

Excel’s XLOOKUP function is a powerful tool that can help you to search for specific data in a table or range of cells.

This guide will provide a comprehensive understanding of using the XLOOKUP formula in Excel, including its syntax, arguments, and examples.

Syntax

The syntax of the XLOOKUP function is as follows:

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

Here is what each argument means:

• lookup_value: This is the value that you want to search for.
• lookup_array: This is the range of cells that contains the values to be searched.
• return_array: This is the range of cells that contains the values to be returned.
• [if_not_found]: This is an optional argument that specifies the value to return if the lookup value is not found. By default, this argument is set to “#N/A”.
• [match_mode]: This is an optional argument that specifies how to match the lookup value with the values in the lookup array. The available options are: 0 (exact match, the default), -1 (exact match or next smallest), and 1 (exact match or next largest).
• [search_mode]: This is an optional argument specifying whether to perform an exact or a fuzzy match. The available options are 1 (exact match, the default), and 2 (fuzzy match).

Excel XLOOKUP Examples

Now, let’s look at some examples to understand how to use the XLOOKUP function in Excel.

Example 1: Exact Match

Suppose you have a list of products and their prices in a table, and you want to find the price of a specific product. You can use the XLOOKUP function to do this as follows:

1. Select the cell where you want to display the result.
2. Type the following formula: =XLOOKUP("Product 1", A1:A5, B1:B5)
3. Press Enter.

In this example, “Product 1” is the lookup value, and the XLOOKUP function searches for it in the range A1:A5. When it finds a match, it returns the corresponding value from the range B1:B5, which is the product’s price.

Example 2: Next Smallest Match

Suppose you have a list of numbers and you want to find the closest number that is less than a specific value. You can use the XLOOKUP function with match mode -1 to do this as follows:

1. Select the cell where you want to display the result.
2. Type the following formula: =XLOOKUP(10, A1:A5, B1:B5, , -1)
3. Press Enter.

In this example, 10 is the lookup value, and the XLOOKUP function searches for the closest number that is less than 10 in the range A1:A5. When it finds a match, it returns the corresponding value from the range B1:B5.

Example 3: Fuzzy Match

Suppose you have a table of employees and their salaries, and you want to find a specific employee’s salary, “Alice”. You can use the XLOOKUP function to do this as follows:

1. Select the cell where you want to display the result, for example, D2.
2. Type the following formula: =XLOOKUP("Alice", A2:A6, B2:B6)
3. Press Enter.

The XLOOKUP function searches for “Alice” in the range A2:A6 and returns the corresponding value from B2:B6, which is 45000. The result is displayed in cell D2.

In this example, “John Smith” is the lookup value, and the XLOOKUP function searches for it in the range A1:A5 using fuzzy matching. When it finds a match, it returns the corresponding value from the range B1:B5.

Example 4: Error Handling

If the lookup value is not found in the lookup array, XLOOKUP returns the “#N/A” error by default. However, you can use the optional argument if_not_found to specify a value to return instead. Here’s an example:

Suppose you have a list of employees and their salaries, and you want to find the salary of an employee who is not in the list. You can use the XLOOKUP function with a custom error message as follows:

1. Select the cell where you want to display the result.
2. Type the following formula: =XLOOKUP("Jane Doe", A1:A5, B1:B5, "Employee not found")
3. Press Enter.

In this example, “Jane Doe” is the lookup value, and the XLOOKUP function searches for it in the range A1:A5. When it doesn’t find a match, it returns the custom error message “Employee not found” instead of the default “#N/A” error.

XLOOKUP and VLOOKUP are both Excel functions that allow you to search for specific data in a table or range of cells. However, XLOOKUP is a newer and more flexible function that offers several advantages over VLOOKUP.

Here are some of the key differences between XLOOKUP and VLOOKUP:

1. Search direction: VLOOKUP can only search from left to right, whereas XLOOKUP can search in any direction. This means that XLOOKUP can be used to look up data in structured tables differently than the traditional VLOOKUP table.
2. Match modes: XLOOKUP offers more match modes than VLOOKUP, including exact match, fuzzy match, and next smallest or largest match. This makes XLOOKUP more versatile than VLOOKUP, as it can handle a wider range of search criteria.
3. Return values: XLOOKUP allows you to return values from any column in the table, whereas VLOOKUP can only return values from the rightmost column. This means that XLOOKUP can be used to return multiple values from a table, which is impossible with VLOOKUP.
4. Error handling: XLOOKUP has better error-handling capabilities than VLOOKUP. XLOOKUP allows you to specify a custom error message to be returned if the lookup value is not found, whereas VLOOKUP returns the “#N/A” error by default.
5. Syntax: XLOOKUP has a simpler and more intuitive syntax than VLOOKUP, which makes it easier to use and less prone to errors.

Overall, XLOOKUP is a more powerful and flexible function than VLOOKUP and should be used whenever possible.

However, if you are working with an older version of Excel or with data that is structured in a way that is not compatible with XLOOKUP, VLOOKUP can still be a useful tool for looking up data in Excel.

Excel VLOOKUP example

Here’s an example of using the VLOOKUP function in Excel, with two tables:

Table 1: Employee List

In this example, we want to look up the salary of an employee in the Employee List table using their Employee ID. Here are the steps to do this using the VLOOKUP function:

1. Create a new column in the Employee List table to hold the salary information. In this example, we’ll add a column with the header “Salary”.
2. In the first cell of the “Salary” column, enter the VLOOKUP function: =VLOOKUP(A2, Salary_List, 2, FALSE). Here, A2 is the cell containing the Employee ID we want to look up, Salary_List is the range containing the Salary List table (including headers), 2 indicates that we want to return the value from the second column of the Salary List table (which contains the salaries), and FALSE specifies that we want an exact match (as opposed to an approximate match).
3. Copy the VLOOKUP formula to the rest of the cells in the “Salary” column.
4. The “Salary” column should now contain the salary information for each employee, based on their Employee ID.

Note that for the VLOOKUP function to work correctly, the Employee ID column in both tables must be sorted in ascending order.