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

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.

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

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 vlookup multi value return

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 values with single 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.

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

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

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

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

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

Leave a Reply

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