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:

## Can I see an example worksheet?

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

which will not work, to this one:

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.

Hi Ben,

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

Thanks a mil!

Edwin Spiessens

Silicone Wristbands 4 Africa

Great! Cheers ðŸ™‚

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.

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.

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:

https://docs.google.com/spreadsheets/d/10quw8ND1uPvffIiq_-0TCUaVDdngGB5V6oEBE9GOOq8/edit?usp=sharing

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

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

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

I’ve added this to the template Google Sheet.

Cheers!

Ben

Is there a way to return different values in a single column from multiple rows the match the first VLOOKUP term? Thanks!

Hi Ben,

Can you tell me how to return multiple text values in the same cell (separated by commas ideally), but from different columns in different sheets?

Thanks

Different Google Sheets? You’ll need to use the IMPORTRANGE formula to get the data you need from other sheets. It’ll be a pretty clunky formula! Something like this maybe:

`=IMPORTRANGE(spreadsheet_key1, "sheet1!A1") & "," & IMPORTRANGE(spreadsheet_key2, "sheet1!B1") & "," & IMPORTRANGE(spreadsheet_key3, "sheet1!C1")`

If you need to do more complex lookups on the data, then you probably want to use IMPORTRANGE to bring all your different Google Sheets data into a single tab and then do analysis/lookups on that.

Hope that helps!

Ben

Hope that helps!

Cheers,

Ben