Have Vlookup Return Multiple Columns in Google Sheets

Have you ever wished the VLOOKUP function could return multiple columns?

For example, maybe you want to return several values that match a search term so that you can use a range of data as an input for another formula (e.g. a sparkline).

Return multiple values with single vlookup formula in Google Sheets

Of course, you could set up multiple vlookup formulas, but this wouldn’t work if you want to pass the data range into another formula, like the sparkline for example.

However, you can easily achieve it with just one formula.

Vlookup return multiple columns

We use curly brackets {} to indicate which columns we want to return and then convert the whole formula into an ArrayFormula to tell Google Sheets we’re working with a range output, not a single value.

What’s the formula to have Vlookup return multiple values?

Assuming I have a data table in range A1:G9 and my search value in A14, as shown in the image above, then the lookup formula is as follows:

=ArrayFormula(VLOOKUP($A$14,$A$1:$G$9,{2,3,6,7},FALSE))

Can I see an example worksheet?

Yes, here you go.

How does this formula work?

Imagine this is your raw data table and you want to search for an ID and return values from multiple columns:

raw data for return multiple columns with vlookup example

Essentially, we perform a regular VLOOKUP but in place of single column index, we put an array of columns we want to return, surrounded by curly brackets, like so:

{2,3,6,7}

which returns the 2nd, 3rd, 6th and 7th columns.

Then we must press Ctrl + Shift + Enter (on PC) or Cmd + Shift + Enter (on Mac) once we’ve entered the VLOOKUP to turn it into an array formula. (You can also just type in the ArrayFormula word.)

So we go from this formula:

=VLOOKUP($A$14,$A$1:$G$9,{2,3,6,7},FALSE)

which will not work, to this one:

=ArrayFormula(VLOOKUP($A$14,$A$1:$G$9,{2,3,6,7},FALSE))

which does work and will output an array (i.e. multiple values). It’s important that the output cells (4 in this case) are all empty for the formula to work.

This is what’s happening:

return multiple columns with vlookup formula in Google Sheets

We search for a single search term and return values from four columns which are output into for adjacent cells. You can select whichever columns you wish as well as changing the order.

Related Articles

2 thoughts on “Have Vlookup Return Multiple Columns in Google Sheets”

  1. Ben, Given your example above, is it possible to sort the results by column 6?

    Thank you for your site and videos….they’ve been an invaluable resource to help me learn!

Leave a Reply

Your email address will not be published. Required fields are marked *