Have you ever wanted Vlookup to 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).

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?

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

You can use FILTER for this

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?

HI Ben,

i have a list of data:

C T R

u9 8 PASS

u9 8 PASS

u9 8 PASS

u13 5 FAIL

u13 5 PASS

i want to divide the quantity of “PASS” from R by T for only “u9”.

Example, 3 PASS for u9 divide by 8.

Any formula can use on this case. Because i only want to detect 1 8 from u9 and divide the PASS which belongs to according C.

Thanks

Hi all,

I need below data, please advise which formula i can use

DATA

ITEM A——-2

ITEM B——-3

ITEM C——-2

ITEM A——-5

ITEM A——-3

ITEM B——-2

ITEM C——-10

ITEM C——-5

I need like this

ITEM A——-2

ITEM A——-5

ITEM A——-3

please help me

Hi V,

Try the FILTER function: https://www.benlcollins.com/spreadsheets/filter-function-google-sheets/

Hi Ben

i somehow im struggling with the formula. Basically i have sheet https://docs.google.com/spreadsheets/d/1s5E2Nbz7_nXZHKVbuZ3rGwqy3YlMauB8XYv8xPfCm-8/edit#gid=0 and would like transferred all data from columns H and I only when the value in G is “c” to https://docs.google.com/spreadsheets/d/1qfSDxcV5yHaK_B8oHvwEHia14TOeU6_DIgxDQoxJzjA/edit#gid=0. Can you please help me out with the formula?

Many many thanks

Hello Ben,

I used the formula which you showed, however, I received an error #NAME?

=ArrayFormula(VLOOKUP([@Status],$I$12:$M$64,{2,3,4,5},FALSE)).

Please help.

Thank you in advance.

hello, i’m new to formulas in google sheets and i somehow took myself into a weird place… i came up with this…

=iferror(ArrayFormula(transpose(vlookup(calendario!$C8,xrecetas!$F$4:$AA$16,{2,3},FALSE))))

my issue is that now i believe i should display {2,3} in the same cell. is there any way to do it?

note: the column in position 2 is a quantity, the column in position 3 is the item.

thanks in advance for any help you could give me

Hi There

I want to drag my vlookup formula across columns but do not want to manually type the column reference everytime I move to the next cell. Is there an easier way to do this?

Yes, you can use the COLUMN() function to do that. You might need to add or subtract a number to get the correct starting column.

Hi Ben,

I have a data set of values spread in multiple columns of single row. My unique identifier is one of the column. I am stubbing an example below:

Student ID; No of subjects; Social; English; Maths, Science, French; Tot

1000;5;60;70;70;50;50;300 – Row 1

1001;2;;;50;;50;100 – Row 2

I want Row 1 to split into 5 individual rows for each subject.

1001; Social; 60;300

1001; English; 70;300

1001; Math;70;300 etc.

I want to insert the rows dynamically based on the number of the subjects count and append as explained above.

What is the best way to do this.

Hello Ben,

I am just wondering if this vlookup can be used to return a different values or even have multiple statements?

As an example,

In Sheet βDataβ I have a list of flowers in column A, and a different list in Column B. In Cell C1 I have an article number for Column A flowers, same for C2.

In my main Sheet, I have a drop box to select what flower, which is in C12, in Cell A12 I need it to return the article number that is for that particular flower, whether it was from Data!C1 or C2. This lookup as is, I believe would work singly, just not with Multiple search Columns.

Also can Ranged Names be used rather than Columns etc?

Thanks.

Hi Benny

I need your assistance if you can this time.

Hello Ben,

What if i want to combined 2 cell. The Problem is the cell are in a different rows. I’ve been searching a solution for this but really can’t do it. Like for example the first answer in in Row 2 column B & C. The next one is in Row 3 column B & D. The other is in Row 4 column B & D and sometimes it will go back to column B & C,sometime column C & D,so fort and so on. Kindly help for this problem

I’m trying to do something that feels similar where I use a Vlookup to return multiple values from rows that are tied together with the same key.

Here’s my sample spreadsheet with a before/after: http://bit.ly/2U3EFWH

Any ideas?

Hi Ben,

I need some quick help please. I have the below data:

AA ————— 5

BB ————— “blank”

CC ————— 4

DD ————— 5

I want a function where I can look for 5 and return all the corresponding values like AA & DD.

I am new to Vlook up and was wondering if it is possible to match based on combinations. I have data where names can be same but country can be different. What I want to do is, if both name and country match, then return the values from a different sheet. I am really struggling with it and will appreciate your support.

It help me a lot, thank you!

Hi Ben,

Thanks for the tutorials.

Do you know if there is a way for me to take the several column values and use them in a proceeding formula, rather than outputting them to the sheet?

Would appreciate any tips!

Nick

Hi Ben

This was exactly what I needed – thanks!

I’ve picked up several useful how-to’s from your site, so I just wanted to give you a shout-out for providing a very helpful resource.

Keep up the good work!

Alex

Thanks, Alex!

Hi Ben

My Table is look like

AS TS KS SP TH

CP CK SK CK MK

TT HT MT CT ST

I WANT TO PRINT THE ENTIRE RAW WHICH CONTAINS ANY OF THE CELL MATCH WITHIN THE RAW

IF I SEARCH “KS ”

MY RESULT SHOULD BE

AS TS SP TH Except ” KS”

Brilliant, just the trick needed, thankyaverymuch!

Hey Ben!

Thanks for putting out such a great example,

1 Question, How do I get a formula to look up all the possible answers and give them to me in one cell?

Eg: (Table A1:B3)

Col. A | Col. B

————————–

Apple | Green

Apple | Red

I want a formula that looks up Apple from Cell C1 and shows the following in C2: Green, Red

Thanks, looking forward to solving this haha.

Hi Ben, your information is excellent! Thank you!

I have a problem and I can’t find a solution. I have 6 sheets. On each sheet is a list of metrics we’re collecting to use for reporting. Each sheet is a category of metric, so sheet 1 will be ‘users and usage’ and will have things like ‘number of users’, ‘number of uses’ and sheet 2 will be ‘inventory info’ and have things like ‘inventory in transit’ or ‘inventory on hand’. Now the challenge. Some of the metrics are in the first version of our report and some are in the second so I’ve created a column called ‘version’ and in that column, on each row is a value of ‘v1’ or ‘v2’. I tried to use vlookup on a new sheet to list all metrics in v1 and then all in v2, along with 2 columns of data so it should look like this:

Column A Column B Column C

Version Metric Category

V1 Number of users Users and Usage

V1 Inventory on hand Inventory

V2 Number of uses Users and Usage

V2 Inventory in transit Inventory

I simply cannot figure out how to pull all the values and create a V1 and V2 list of individual metrics and their associated category. Have I exceeded the bounds of what’s possible with a formula?

Thanks for such a great tips! Just wondering for the returned results, how can I skip the blank cells and return just the cells with text?

Hi Ben!

Awesome work. I was banging my head against the wall until I came across this.

I do have one question though – Instead of the array being {2,3,4,5,6}, is there a way to set a range of {2 to 5}? I have a good number of columns to populate and not having to type ever column number would be very helpful.

Thanks in advance and keep up the great tutorials!

Jon

I have this question too!

me too

I found a solution for my particular case to use a specified range as input instead of hardcoding.

Here is before using hardcoded columns:

=TRANSPOSE(ArrayFormula(VLOOKUP(C1,Query!1:1000,{1,2,3,4,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,42,43,44,45,46,47,48,49,50},FALSE)))

Here is after using arrayformula, column, and indirect:

=TRANSPOSE(ArrayFormula(VLOOKUP(B1,Query!1:1000,arrayformula(column(indirect($A$7))),FALSE)))

In cell A7 I have “Query!$A$1:$AG$1”. The range spans all columns I needed and returns the numbers in a set just as hardcoded. In my case there were no gaps.

Hope this helps. Thanks to ben for one of his comments about Column() which led to this solution.

Dear Ben

My Table is look like

AS TS KS SP TH

CP CK SK CC MK

TT HT MT CT ST

I want to print the entire raw which contains any of the cell match within the raw

If I search βKS β

My result should be

AS TS SP TH except β KSβ

OR

If I search βHTβ

My result should be

TT MT CT ST except β HTβ

Hi Ben

While I have been using the Vlookup functions succcessfully, I got struck on this. I have a large table with similar figures against different categories. I want to retrieve the most matching figure from the table against a particular category.

Example: I have arrived to a figure of 7896 against a particular employee under a particular category. I want vlookup to pick a closest figure from the range of data that I have. It may not be the exact figure but the closest one will do.

Can you please help me???

i’ve been looking this for years. Thanks a lot from Spain!!

sorry Guys but i did exactly the same thing in google sheet but the result was vertical and not horizontal ; the formula tooks the right info from columns , but the result is putted under the first reference . i m becoming crazy

PS : I m using VLOOKUP and Not HLOOKUP

Thanks for your help

If your sheet configuration is not = United States, the formula must be adapted. Example in French the commas in “;” and to have the columns the separator between brace => {2 \ 3 \ 4 \ 5 \ 6}

Yes, indeed! See here for more details: https://www.benlcollins.com/spreadsheets/sheets-location/

This is exactly what I needed, thank you! Is there any way to do the same thing with the INDEX MATCH combination? I’ve heard that combination is better to use than VLOOKUP, but it doesn’t seem to be working for me when I try to use it to return multiple columns.

in 1 work sheet, what formula can i use:

What fruit has the least number of unit sales?

Fruit Unit Sales

Apple 33.0

Orange 30.0

Jackfruit 34.0

Coconut 49.0

Banana 80.0

Raspberry 46.0

Grapes 55.0

Watermelon 22.0

Strawberry 44.0

Dragon fruit 45.0

sorry new in excel π

Ben ,

This is my format.

Host CVE1 CVE2 CVE1 CVE3 CVE4

1000 Completed Completed Completed

2000 Pending Pending Pending Pending

3000 Completed Completed Completed Completed Completed

4000 Pending Pending Completed Completed Completed

5000 Pending Pending Completed Completed Completed

My result should Like this

Result

1000 CVE1 Completed

2000 CVE2 Pending

2000 CVE1 Pending

here We have Column name as duplicates ( CVE1 is repeating ) – It should ignore the Blank value and extract the value from it ( Same Column name ).

Just want to say thank you, it works!