Have you ever wanted to return multiple columns with 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 columns with 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 to return multiple columns with VLOOKUP?

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?

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

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

Hi Ben,

Thank you for your vlookup tips. I was wondering how would you retrieve multiple vlookup values listed across over 100 Google sheets contained within a workbook.

In MS excel, I accomplish this easily by searching all sheets via a sheet which lists all the sheets to be searched.

So what I am trying to achieve is – the vlookup should look across all the sheets in the workbook and retrieve the associated column values, like in your example.

Ideally I do not have to name all the sheets to be searched, because 1) there are way too many 2) there will be more added with time.

If my poor explanation above does not make sense, I can paste a googlesheets link.

Many thanks for your time.

Shirish

Hey Shirish,

Have you tried your Excel solution in Google Sheets? Usually what works in one, works in the other, so I’d try it first and see if it works in Sheets for you.

Ben

Thank you Ben for taking the time.

In Excel I create a new worksheet which contains the names of all the worksheets that need to be searched. Name the range of the names of the worksheets.

I use the following formula to search all the sheets which are contained in the list.

{=VLOOKUP(A2,INDIRECT(“‘”&INDEX(Sheetlist,MATCH(1,–(COUNTIF(INDIRECT(“‘”&Sheetlist&”‘!$A$2:$B$5″),A2)>0),0))&”‘!$A$2:$B$5”),2,FALSE)}

The process I use in excel is not available in Google sheets unfortunately.

Now I am using a painful workaround which involves merging all the sheets’ data into one sheet using the following formula and then doing a vlookup on the single consolidated sheet.

=QUERY({Sound!A2:L;Light!A2:L;’Some Natural Phenomena’!A2:L;Adolescence!A2:L}, “select * where Col2 is not null”,0)

Leads me to a question, is there any way of consolidating data on all sheets, without having to name them individually as I have had to in the formula above? Else I guess it will involve a days work just to get the data entry done correctly!

In any case, many thanks for your time and I will recommend your courses to my colleagues and friends who struggle in one way or other with spreadsheets.

Hey Shirish,

You could use named ranges, otherwise I think you’ll still have to reference your sheets by name. In Excel you still have a sheet with a list of all the sheet names right? So it’s the same in sheets, if you can create that list of sheet names and ranges, then you can easily reference that in your formulas, using something like this (essentially what you have with sort instead of query):

`=ArrayFormula(vlookup(A1,sort({indirect($D$1);indirect($D$2);indirect($D$3)},1,true),2,false))`

where D1, D2, etc. are a list of our sheets and ranges e.g. Sheet1!A2:B, Sheet2!A2:B, Sheet3!A2:B

You could also replace the

`indirect()`

with named ranges.If you can, you could set your sheets names/ranges so that they just increment, then you can build the list of them with a formula too, e.g.

`="Sheet"&row()&"!A2:B"`

Hope that helps!

Ben

Ben, I’m getting all-but-perfect results with this formula:

=ArrayFormula(vlookup($D$10,September!$A$2:$AJ$2,{5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41},FALSE))

The 5,6,7,8, etc. each represent the 1st, 2nd,3rd…31st days of the month. (The 1st day of the month the header 5, the last day of the month, the header 41 on the lookup sheet/table.)

The formula works perfectly except it consistently does not return the values of the last two days of the month, i.e., the 30th (30) and the 31st (31 in the sequence).

Is there a max. number of values that can be returned using this formula in Google sheets, or have I just missed something?

Thank you very much!

Just writing out my problem solved it! I’m very sorry to have posted prematurely, Ben.

No worries! I find that happens all the time too ðŸ˜‰

Hi Ben,

thanks for all that usefull tips. How do I need to adjust the formular if I want to have multiple results? So not only one row is returned, but when I drag down the formular, it should continue with the next match if I am for example looking for a value which is often in the spreadsheet.

Dear Ben,

Great post. it has helped me a lot.

I’ll just like to add that in the search key instead of single cell a column can also be given and output will be an array having that many rows.

To make it more clear

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

Can also be written as

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

And it will give all the results for row 14-20.

That saves tons of work at many a times.

Regards

Nice one! Thanks for sharing Sanjay.

Hello Ben,

Thanks for this tip! It’s great.

A question. I’m trying to vlookup from bottom up, that is, I want to match not the first match, as vlookup does, but the last one.

I find it working perfectly with this formula: =Index(A:B, Max(Filter(Row(A:A), A:A=D2)),2), equivalent of =Vlookup(D2,A:B,2,false). However, when trying to do your trick of this article, =Index(A:B, Max(Filter(Row(A:A), A:A=D2)),{2,3}) gives me only the value for index 2, not for both 2 and 3.

And then, trying to array vertically too (not only horizontally as your article here teaches), =ArrayFormula(Index(A:B, Max(Filter(Row(A:A), A:A=D2:D)),2)) doesn’t give me any result.

Any thought or idea how I could achieve what I’m trying to do (vlookup with last match)?

Thanks,

CÃ©dric

Hey CÃ©dric,

Good question and, as far as I can tell, there doesn’t appear to be a way to use this trick inside of the INDEX function.

You could try using a QUERY function to select only the columns you want after the INDEX returns them, e.g. like this (my range was columns A to G, and search term in cell I2, and I wanted data from B, C, F and G back):

`=query(Index(A:G, Max(Filter(Row(A:A), A:A=I2))),"select B,C,F,G")`

Not sure if this would have a performance impact though, if you have thousands of formulas, so worth keeping that in mind…

Cheers,

Ben

So can I use vlookup to look up a text value in column A and if it matches to concatenate and populate an adjecent cell with the values from cells within the same column?

Also the first

Hi, Ben,

Thank you for sharing this. Can it work if there are multiple matches? In testing, it only returns the first match.

In my scenario, I’m looking up in . This formula works outside of array, but I can’t get it to work properly in an array: (vlookup(D2, sort(INDIRECT(“L$2:M”&(IF(row()=2, 2, row()-1))), 1, true, 2, true),2, 1). For context, I’m asking it to only look at the rows above the current row because I’m creating a subtotal column.

Sorry the page wouldn’t show comments enclosed in symbols greater than, less than. Supposed to say:

I’m looking up “search term column D” in “range L:M”

Hi Denise,

Yes, unfortunately the vlookup will only return the first match. It doesn’t look any further. If you want to return multiple rows of data based on conditions, then you might try looking at the Filter or Query functions and approach it that way?

Ben

I’ve been using this array formula with vlookup, with no problems, now all the sudden one of my spreadsheets isn’t allowing me to “sort” my data without moving the data… specifically away from their rows and down into empty, other rows. This is very concerning, considering I can’t sort the data anymore without data moving! Help! (I am clicking the top left box to grab the entire spreadsheet before sorting, so this isn’t the issue.) Thanks!

Hi Ben,

I need your help i have two worksheets one worksheet has ALL types accounts# in same column and another has two columns one has accounts# for one type and another has account# of other type i want to compare either or matching with (first worksheet which has ALL types of accounts#) one worksheet and get the value of customer name and customer company name in return from the range.

I hope you understood what i am trying to explain you.

Thanks

Regards,

Niky

Dear Ben,

my problem is i have belongs to transport deprt, i have sheet in that one part purchase in multibule times so when i am entaring part no that can show me a all the detail of purchasing list ( list that can show me how many time purchasing with dates and price).

I hope you understood what i am trying to explain you.

you can send me by email also . email id mention below.

This is.

Imran Syed

Hi Ben,

Good tutorial on VLOOKUP.

I have a case where I need to create a search engine for relevant values. Example:

Put an input number in a cell (e.g. 5) and search for all matching products with the value 5 (or close) as well as displaying the number of the products having the number 5. The list I have is composed of at least 2 columns.

Is this possible with the VLOOKUP function?

Thanks,

Ivan

This is great! Now is there a way to show every result with the matching Vlookup? I want to create a “search feature” that returns all records of that type with all of the columns.

Hi Ben,

Following formula is not working. Though data is in a single worksheet, but in two sheets.

=ArrayFormula(VLOOKUP($A$2:$A$62150,Sheet2!$B$2:$H$6225,{2,3,4,5,7},0))

Any solutions. please

Hi Ben,

I have a long list of products, i have an ArrayFormula with VLookup working however, when the list of products ends, it keeps going for however many empty cells there are after. Is there a way to tell the ArrayFormula to leave the cells blank if there are currently no values? Heres my current formula…

=ArrayFormula( VLOOKUP( B2:B , Importar!A2:B , 2 , FALSE ) )

Thanks in advanced?

Hi Dan,

You need to include an IF statement in your array formula to check for blanks. Have a look at this article where you can see how it works: https://www.benlcollins.com/formula-examples/array-formulas-forms/

Cheers,

Ben

hi, i want to write your formula in vba but it doesn’t let me, do you know how to code it in a excel macro?