# How to return multiple values with VLOOKUP in Google Sheets?

Have you ever wanted to return more than just one value with your VLOOKUP?

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).

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.

# How do we return multiple values with a single VLOOKUP?

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?

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))

## 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:

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:

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.

## 8 thoughts on “How to return multiple values with VLOOKUP in Google Sheets?”

1. Hi Ben,

Great stuff! I copied your sample sheet, worked like a charm!
Thanks a mil!

Edwin Spiessens
Silicone Wristbands 4 Africa

1. Ben says:

Great! Cheers ðŸ™‚

2. Tiffany M says:

Hi Ben,

Will this formula work if I am trying to merge information from 2 sheets? I received the error #NAME? when I used the formula:
=arrayformula(VLOOKUP(NAR!J2, DecisionManagerDetailReport!M:FH,{2,3,4},FALSE))

NAR & DecisionManagerDetailReport being the names of the 2 sheets I’m trying to merge.

I have all of a customer’s credit card details in one spreadsheet and all of our internal account information in a separate sheet. Both sheets contain their email address I can use for reference. Trying to see if I can find any payment trends in fraudulent accounts we receive.

Thank you!
Tiffany M.

3. Tiffany M says:

So essentially, if an email in the NAR sheet matches one listed in Decision Manager Detail Report, I want it to return the payment details listed within the Decision Manager Detail report to the NAR sheet.

1. Ben says:

Hey Tiffany,

Yes, you can do this, you just need to get your references set up. Not sure why it’s not working for you, but here’s a quick example using the same sheet names so you can see how it works:

Note, you must have these two sheets in the same file. If they’re in different files, then this won’t work and you’ll need to use the IMPORTRANGE() formula first to bring data from one sheet file into the other.

Hope that helps.

Cheers,
Ben

4. Sue says:

How do you format the same Vlookup function only list the results in different rows, listing down not across ? Thanks in advance. Sue

1. Ben says:

Hi Sue,

You can add the TRANSPOSE function into the mix to change the listing from across to down. The formula is now:

`=ArrayFormula(TRANSPOSE(VLOOKUP(\$A\$14,\$A\$9:\$G\$17,{2,3,6,7},FALSE)))`