Two Way Lookup Using XLOOKUP (Horizontal & Vertical Lookup)

XLOOKUP is a powerful function in Excel that allows you to perform both horizontal and vertical lookups. It was introduced in Excel 2019 and Excel for Microsoft 365, and it provides more flexibility and functionality compared to the traditional VLOOKUP and HLOOKUP functions. In this long answer, I’ll explain how to use XLOOKUP for both horizontal and vertical lookups with examples.

Vertical Lookup: A vertical lookup searches for a value in the leftmost column of a table or range and returns a corresponding value from a specified column. Here’s the syntax for the vertical XLOOKUP:

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

Let’s break down the parameters:

1. lookup_value: This is the value you want to find in the leftmost column of the lookup_array.
2. lookup_array: This is the range of cells that contains the values to be searched. The leftmost column of this range is where the lookup is performed.
3. return_array: This is the range of cells that contains the values you want to retrieve. The corresponding value is returned from this range.
4. if_not_found (optional): This parameter specifies the value to return if the lookup_value is not found. You can leave it blank to get an error.
5. match_mode (optional): This parameter determines the type of match to be performed. The available options are 0, -1, or 1, corresponding to an exact match, a match that is less than the lookup_value, or a match that is greater than the lookup_value, respectively. The default is 1.
6. search_mode (optional): This parameter determines the search direction. The available options are 1 or -1, corresponding to a search from top to bottom or bottom to top, respectively. The default is 1.

Now, let’s look at an example to illustrate the usage of vertical XLOOKUP. Suppose we have a table with employee names in column A and their corresponding salaries in column B. We want to find the salary of an employee named “John.” Here’s how we can use XLOOKUP:

=XLOOKUP("John", A2:A10, B2:B10)

This formula searches for the value “John” in the range A2:A10 and returns the corresponding salary from the range B2:B10.

Horizontal Lookup: A horizontal lookup searches for a value in the topmost row of a table or range and returns a corresponding value from a specified row. The syntax for the horizontal XLOOKUP is similar to the vertical lookup, with a slight variation:

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

The parameters have the same meaning as in the vertical lookup, but the lookup_array and return_array are transposed, which means the lookup is performed horizontally.

Let’s consider an example to understand the usage of horizontal XLOOKUP. Suppose we have a table with product names in row 1 and their corresponding prices in row 2. We want to find the price of a product named “Widget.” Here’s how we can use XLOOKUP:

=XLOOKUP("Widget", A1:D1, A2:D2)

This formula searches for the value “Widget” in the range A1:D1 and returns the corresponding price from the range A2:D2.

XLOOKUP also allows you to perform more advanced lookups, such as approximate matches, wildcard matches, and array lookups. However, those features are beyond the scope of this explanation.

Remember that XLOOKUP is a dynamic array formula, which means it can return multiple results at once. This is particularly useful when you’re searching for a value that appears more than once in the lookup_array.

Let’s take a look at an example of using XLOOKUP for a vertical lookup with multiple results. Suppose we have a table with student names in column A and their corresponding grades in column B. We want to find all the grades for a student named “Sarah.” Here’s how we can use XLOOKUP:

=XLOOKUP("Sarah", A2:A10, B2:B10)

In this case, if “Sarah” appears multiple times in the lookup_array, the XLOOKUP formula will return an array of all the corresponding grades. You can simply enter the formula in a range of cells, and Excel will automatically populate the results.

Similarly, for a horizontal lookup with multiple results, let’s consider a table with product names in column A and their corresponding prices in column B to E. We want to find all the prices for a product named “Widget.” Here’s how we can use XLOOKUP:

=XLOOKUP("Widget", A1:A10, B1:E10)

Again, if “Widget” appears multiple times in the lookup_array, the XLOOKUP formula will return an array of all the corresponding prices. Enter the formula in a range of cells horizontally, and Excel will populate the results accordingly.

Dynamic array formulas can spill the results into adjacent cells automatically, so you don’t need to select a range of cells before entering the formula.

It’s important to note that XLOOKUP requires Excel to support the dynamic array feature. If you’re using an older version of Excel, you might not have access to XLOOKUP or dynamic arrays. In that case, you can consider using other lookup functions like VLOOKUP or HLOOKUP.

That’s a brief explanation of how to perform vertical and horizontal lookups using XLOOKUP in Excel. Remember to adjust the ranges and parameters based on your specific data and requirements.