Microsoft Excel's XLOOKUP function is widely recognized for its ability to fetch a value from a specific row or column in a dataset. However, this versatile tool can also be used to perform two-way lookups, where it returns a value at the intersection of a specified row and column.

Microsoft 365 Personal Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.

$99.99 at Microsoft$99.99 at Amazon Expand Collapse Using XLOOKUP for One-Way Lookups in Excel

To fully grasp how XLOOKUP can execute two-way lookups, it's essential to first understand how the function operates in one-dimensional situations.

Related ##### Forget VLOOKUP in Excel: Here's Why I Use XLOOKUP

Out with the old, and in with the new.

Posts5 The XLOOKUP function comes with six arguments:

<code>=XLOOKUP(<em>a</em>,<em>b</em>,<em>c</em>,<em>d</em>,<em>e</em>,<em>f</em>)</code>

where

• a (required) represents the lookup value,
• b (required) denotes the lookup array,
• c (required) indicates the return array,
• d (optional) specifies the text to return if the lookup value (a) isn't found in the lookup array (b),
• e (optional) defines the match mode (0 = exact match; -1 = exact match or next smaller item; 1 = exact match or next larger item; 2 = wildcard match), and
• f (optional) determines the search mode (1 = top to bottom or left to right; -1 bottom to top or right to left; 2 = binary search with the lookup array (b) in ascending order; -2 = binary search with the lookup array (b) in ascending order.

Using XLOOKUP for Two-Way Lookups in Excel Although one-way lookups are effective in many cases, they don't provide the flexibility to change the variable you're searching for. For instance, the formula mentioned earlier only retrieves a score based on a given ID. But what if you wanted to find out the age or gender instead? This is where two-way lookups—also referred to as two-dimensional or matrix lookups—become useful.

To achieve this, you need to nest one XLOOKUP within another:

<code>=XLOOKUP(<em>a</em><em>1</em>,<em>b</em><em>1</em>,XLOOKUP(<em>a2</em>,<em>b2</em>,<em>c</em>))</code>

where

• a1 and b1 represent the lookup value and array for the first variable,
• a2 and b2 denote the lookup value and array for the second variable, and
• c stands for the return array.

Pro Tip: Add Drop-Down Lists to the Lookup Cells Once your two-way lookup is set up, consider enhancing it by adding drop-down lists to the cells that contain lookup values, streamlining the data retrieval process.

Related ##### How to Add a Drop-Down List to a Cell in Excel

It's much better than manually entering the same options repeatedly.

PostsSelect one of the lookup cells (for example, cell G1), and then navigate to the Data tab on the ribbon and click "Data Validation."

In the Data Validation dialog box, choose "List" from the Allow field. Then, activate the "Source" field and select the cells containing the values you'd like to appear in the drop-down list. Here, those would be the column headers from B1 to D1.

Upon clicking "OK," a drop-down button will appear in cell G1, offering a menu of variable options when clicked.

Repeat the procedure for the other lookup cell, which in this case is F2.

Notice that in the Source field, I've selected a range extending beyond the current dataset size to accommodate future growth (the dataset contains 100 rows, but I've extended the source to A1000). The period (referred to as a trim ref operator here) after the colon instructs Excel to remove any blank cells at the end of the source range.

Alternatively, you could use a formula that counts the number of non-empty cells in column A and displays that number of options in the drop-down menu.

Related ##### How to Create a Drop-Down List from a Column of Data in Excel

The approach you take depends on the structure of your data.

PostsNow, you have the option to choose an ID from the drop-down list in cell F2.

---

Another method to perform two-way lookups in Excel involves using INDEX and MATCH. This alternative proves especially helpful if you're working with Excel 2019 or an older version since these functions were introduced before XLOOKUP.

The above is the detailed content of How to Perform a Two-Way Lookup in Excel With XLOOKUP. For more information, please follow other related articles on the PHP Chinese website!