Two-Way Lookup and Return Multiple Columns in Google Sheets

Published on

Using OFFSET with XLOOKUP and XMATCH, you can easily perform a two-way lookup and return multiple columns in Google Sheets. Here, “multiple columns” refers to all columns from the intersection point across the range.

For example, you can search for a fruit name in the first column of a range and a month name across the first row, then return the quantity of that fruit for the given month and all subsequent months.

Generic Formula

=OFFSET(XLOOKUP(A10, column, data), 0, XMATCH(A11, row)-1)

Where:

  • column is the first column in the data.
  • row is the first row in the data.
  • A10 contains the search key for the column.
  • A11 contains the search key for the row.

Let’s apply this two-way lookup formula in a real-life scenario to return multiple columns of data.

Example: Two-Way Lookup and Returning Multiple Columns in Google Sheets

In the following example, we have student names in column A, dates in row 1, and the attendance status (present/absent) of each student in the corresponding cells.

We’ll apply a two-way lookup to search for a student in the first column and a specific date in the first row, then return their attendance data from that point onward.

Example of a Two-Way Lookup and Returning Multiple Columns in Google Sheets

This method helps extract the attendance of a specific student from a given date onward, making it useful for analyzing a portion of the dataset.

Formula:

=OFFSET(XLOOKUP("Adam", A1:A6, A1:H6), 0, XMATCH(A11, A1:H1)-1)

The formula searches for “Adam” in A1:A6 and the date in A11 within the first row, then returns the attendance records from that point onward.

Formula Breakdown

This formula is essentially an OFFSET function:

OFFSET(cell_reference, offset_rows, offset_columns, [height], [width])

Where:

  • cell_reference: XLOOKUP("Adam", A1:A6, A1:H6) – Finds the row corresponding to “Adam”.
  • offset_rows: 0 – Keeps the lookup on the same row.
  • offset_columns: XMATCH(A11, A1:H1)-1 – Determines the column offset by matching the date in the header row. Subtracting 1 aligns it with the 0-based index system used by OFFSET.

Alternative Solution: Two-Way Lookup to Return Multiple Columns

You can replace XLOOKUP with INDEX-MATCH and XMATCH with MATCH as an alternative.

Alternative Formula:

=OFFSET(INDEX(A1:H6, MATCH(A10, A1:A6, 0)), 0, MATCH(A11, A1:H1, 0)-1)

Where:

  • cell_reference: INDEX(A1:H6, MATCH(A10, A1:A6, 0)) – The MATCH function locates “Adam” in the first column, and INDEX retrieves the corresponding row.
  • offset_rows: 0 – No row offset.
  • offset_columns: MATCH(A11, A1:H1, 0)-1 – Finds the column position of the date.

Additional Tip: Two-Way Lookup and Return Two Columns

The above formulas return all the columns after the intersection point of the two search keys. However, sometimes you may only need to return two columns.

For example, in an employee dataset similar to the one above, instead of just present and absent statuses, you might have an overtime column after each date as well.

In such cases, the purpose of the two-way lookup and returning multiple column values will be limited to two columns.

You can achieve this by specifying the width argument in the OFFSET function.

First Formula:

=OFFSET(XLOOKUP("Adam", A1:A6, A1:H6), 0, XMATCH(A11, A1:H1)-1, 1, 2)

Second Formula:

=OFFSET(INDEX(A1:H6, MATCH(A10, A1:A6, 0)), 0, MATCH(A11, A1:H1, 0)-1, 1, 2)

Can We Use VLOOKUP in This Case?

No, VLOOKUP cannot be used within OFFSET because it does not return a direct cell reference.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Filter the Bottom 10 Items in a Pivot Table in Google Sheets

This tutorial explains how to filter the bottom 10 items in a Pivot Table...

Hyperlink to Jump to the Last Used Row in Excel

In a vertical range, you can create a hyperlink to jump to the last...

Find the Last Used Row’s Last Value Address in Excel

In a large vertical dataset in Excel, how do you find the cell address...

Find the Last Used Row Number in Excel

When working with large datasets such as sales records, purchase data, or bills of...

More like this

Filter the Bottom 10 Items in a Pivot Table in Google Sheets

This tutorial explains how to filter the bottom 10 items in a Pivot Table...

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

2 COMMENTS

  1. What would be the formula to do a 2-way Lookup and Sumproduct the inside i.e. multiple June values for the “banana”?

    • Hi, Jamie Chung,

      Why don’t you wrap the formula with Sumproduct? Both solutions would work in that way.

      Eg.:

      =sumproduct(OFFSET(index(B2:N6,match(F9,B2:B6,0)),0,match(F10,B2:N2,0)-1))

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.