How to return multiple columns in a two-way lookup in Google Sheets. Let’s go to that details into this tutorial. To do a two-way lookup and return multiple columns, you can use Vlookup-Match or Index-Match in Google Sheets.

Many Google Sheets, as well as Excel users, are familiar with Index-Match. But how to return multiple columns in Index and Match is not well conversant to many of them.

Similar: Multiple Conditions in Index Match in Google Sheets.

In this Google Sheets tutorial, I am going into that details. Actually, I have multiple solutions for multiple column outputs in a two-way lookup. I am posting the popular two.

See this screenshot to know what I meant by two-way lookup and return multiple columns in Google Sheets.

Here in this example, I want to look up the search key “Banana” in column one and “Jun” in row one. Then I want the formula not only to return the intersecting value but also the values from a few adjoining columns.

To know how to do a two-way lookup, please do check my concerned tutorial – How to Perform Two-way Lookup Using Vlookup in Google Sheets.

In this tutorial, I am only dealing with how to return multiple columns in a two-way lookup in Google Sheets.

Two-way Lookup and Return Multiple Columns in Google Sheets

Let me start with Vlookup and Match combo formula. First of all, see how the Vlookup-Match formula returns a matching value. Then we can move to multiple column outputs in Vlookup-Match.

The above formula only returns a single column value. But I want the values from 6 more columns as entered manually in the screenshot)

See how to return multiple columns in a two-way lookup in Google Sheets.

Vlookup – Match to Do a Two-way Lookup and Return Multiple Columns

In the above formula, the match part returns the number 7 which is the index column number 7 in Vlookup.

To get 6 more additional columns we should specify that in Vlookup. See the generic formula first.

=ArrayFormula(vlookup(F9,B2:N6,{7,8,9,10,11,12,13},0))

Since we want the formula to return multiple columns, we must wrap the Vlookup formula with ArrayFormula. We already know we can use Match to find the index column number 7. Now see how to get the rest of the column numbers in Vlookup.

See how to generate the column index numbers 7 to 13 using a formula that involves the Match.

Formula:

=match(F10,B2:N2,0)

Output: 7

Formula:

ArrayFormula(TRANSPOSE(Row(indirect(match(F10,B2:N2,0)&":"&match(F10,B2:N2,0)+6))))

Output:

7 8 9 10 11 12 13

Generic Version:

=ArrayFormula(TRANSPOSE(ROW(7:13)))

Must Check: Google Sheets Functions Guide.

Now take a look at the formula in the above screenshot. In that replace the Match formula with the just above formula (not the generic one). See that final formula below.

Google Sheets formula to do a two-way lookup and return multiple columns:

=ArrayFormula(vlookup(F9,B2:N6,TRANSPOSE(Row(indirect(match(F10,B2:N2,0)&":"&match(F10,B2:N2,0)+6))),0))

Index-Match to return multiple columns in a two-way lookup in Google Sheets

Index-Match the all-time famous Vlookup alternative can also return multiple columns. Let me show you how to return multiple columns with INDEX and MATCH in Google Sheets.

Here is the Index-Match formula that returns the intersecting value and that would be a single column output.

=index(B2:N6,match(F9,B2:B6,0),match(F10,B2:N2,0))

It would return the value 512 which is in cell H5 (please refer the above screen capture). Now let me share you how this formula can return the value in the range H5: N5.

To return multiple columns using INDEX and MATCH, I am going to use the OFFSET function together with it.

I am taking you to each step in developing the Index-Match formula that can do a two-way lookup and return multiple columns in Google Sheets

Step 1:

=index(B2:N6,match(F9,B2:B6,0))

Output:

Banana 550.00 575.00 545.00 570.00 587.00 512.00 562.00 559.00 567.00 568.00 541.00 641.00

It returns the entire values in the row. We should offset 6 columns to get the required number of columns in the output. We can use the function Offset for that.

Step 2:

Modify the above formula as below.

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

That’s all. If you want to see my example sheet, follow the below link. Thanks for the stay. enjoy.

II Two Way Lookup