When you need to look up one search key vertically and another horizontally, you can rely on XLOOKUP in Google Sheets. The key to performing a two-way lookup with XLOOKUP is replacing the result range of one XLOOKUP with another XLOOKUP.

Generic Formulas

=XLOOKUP("Tuesday", column, XLOOKUP("Period 3", row, data))

Where column and row are the lookup column and row (usually the first column and row of a table), and data is the entire table range. ‘Tuesday’ is the vertical search key, and ‘Period 3’ is the horizontal search key.

Alternatively, you can use:

=XLOOKUP("Period 3", row, XLOOKUP("Tuesday", column, data))

Both formulas will return the same result.

Example of Two-Way Lookup with XLOOKUP in Google Sheets

Assume a school schedule is stored in the range A1:G6, where column A lists weekdays, row 1 contains period names, and the grid (B2:G6) holds the assigned subjects and teachers.

Here’s how to look up the subject and teacher assigned to a specific period on a specific weekday:

=XLOOKUP("Tuesday", A1:A6, XLOOKUP("Period 3", B1:G1, B1:G6))

The inner XLOOKUP searches for “Period 3” in B1:G1 and returns the corresponding column (e.g., column D1:D6).

The outer XLOOKUP then searches for “Tuesday” in A1:A6 and returns the corresponding value from D1:D6.

Alternatively, you can switch the lookup order:

=XLOOKUP("Period 3", A1:G1, XLOOKUP("Tuesday", A1:A6, A1:G6))

Here, the inner XLOOKUP searches for “Tuesday” in A1:A6 and returns the corresponding row (e.g., row 3: A3:G3).

The outer XLOOKUP then searches for “Period 3” in A1:G1 and returns the corresponding value from A3:G3.

Advantages of Two-Way Lookup with XLOOKUP in Google Sheets

One advantage of this approach is its simplicity. The function is easy to understand since it simply replaces the result range of one XLOOKUP with another XLOOKUP. Another benefit is that you can use wildcard matching by setting match mode 2 in XLOOKUP.

A key advantage of two-way lookup with XLOOKUP is that the result can act as a reference. This means you can use it with OFFSET to look around that value. For example:

=OFFSET(XLOOKUP("Tuesday", A1:A6, XLOOKUP("Period 3", B1:G1, B1:G6)), 0, -1)  "Returns the previous period"
=OFFSET(XLOOKUP("Tuesday", A1:A6, XLOOKUP("Period 3", B1:G1, B1:G6)), 0, 1)  "Returns the next period"
=OFFSET(XLOOKUP("Tuesday", A1:A6, XLOOKUP("Period 3", B1:G1, B1:G6)), -1, 0) "Same period on the previous day"
=OFFSET(XLOOKUP("Tuesday", A1:A6, XLOOKUP("Period 3", B1:G1, B1:G6)), 1, 0)  "Same period on the next day"

Resources

• How to Perform Two-Way Lookup Using VLOOKUP in Google Sheets
• Replace VLOOKUP and HLOOKUP with MATCH and INDIRECT in Google Sheets
• Two-Way Lookup and Return Multiple Columns in Google Sheets
• XLOOKUP and Offset Results in Google Sheets
• Slicing Data with XLOOKUP in Google Sheets
• Two-Way Filter in Google Sheets: Vertical & Horizontal
• Nested XLOOKUP Function in Google Sheets