For some of us we use the VLOOKUP function all the time and for the most part is does exactly what we want, but what if you want to lookup multiple columns?

Taking the example below; in cell B3 I have a data validation list that allows me to choose the player I want to look up.

Then in cell C3 I have the SUM of the Pay Rises for 2004 through to 2006 for that player. i.e. the values in columns D, E and F.

Excel VLOOKUP Multiple Columns

With a bit of help from an Array formula we can use our trusty VLOOKUP to do just this.

VLOOKUP Multiple Values Formula

In cell C3 I used the following formula to achieve this multiple VLOOKUP result:

{=SUM(VLOOKUP(B3,Table1[[Name]:[Pay Rise 2006]],{4,5,6},FALSE))}

Note: This is an array formula and so the curly brackets at the beginning and end are entered by Excel automatically when you enter the formula by pressing CTRL+SHIFT+ENTER but you need to type the curly brackets in the middle around the {4,5,6} as Excel doesn’t automatically enter these. More on Excel array formulas.

The Syntax for the above formula is:

=SUM(VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]))

Breaking each component of the formula down:

  1. lookup_value: B3 – This is the name we choose from the data validation list.
  2. table_array : Table1[[Name]:[Pay Rise 2006]] – Our table or data range is an Excel Table hence the data range has the name ‘Table1’. You could easily replace this reference with a regular data range e.g. $A$8:$F$34 or a named range.
  3. col_index_num: {4,5,6} – Usually the column index number will be just one column in your table, but because we want to reference 3 columns, (Pay Rise 2004, 2005 and 2006), we’ve used an array which houses the 3 columns we want to reference (the array is defined by the curly brackets).
  4. [range_lookup]: FALSE – this simply instructs Excel to find an exact match for the
  5. SUM – Sum the results from columns 4, 5 and 6.

If you liked this please click the Facebook Like button below and then sign up for our Free Excel Newsletter for more tips like this.

Share This

Please share this or leave a comment and I'll make sure you get a personal reply.

Leave a Comment

Current day month ye@r *

{ 110 comments… read them below or add one }

Keri Smith December 7, 2011 at 11:58 am

Thank you Mynda for all your tips – I need to run through several of your online training modules. In particular, I always seem to have issues with the VLOOKUP features in Excel – thanks for all your tips!

Reply

Mynda Treacy December 7, 2011 at 7:47 pm

Thanks Keri. I appreciate your feedback.

Kind regards,

Mynda.

Reply

Wayne December 13, 2011 at 10:13 am

Thanks for the tip Mynda, just what i was looking for. I keep getting a #REF! error when a value is in the range, i will continue to play around and modify ;)

Thanks again!

Reply

Mynda Treacy December 13, 2011 at 10:21 am

Cheers, Wayne. Let me know if you’re still getting #REF! and I’ll take a look.

Kind regards,

Mynda

Reply

Rajesh Peter December 15, 2011 at 4:26 pm

Its Cool… I was doing all the time 3 Vlookup for the same, now i got the Idea, how to do it. I’m impressed, Great Tips!!!

Reply

Mynda Treacy December 15, 2011 at 7:41 pm

Cheers. Glad you liked it.

Kind regards,

Mynda.

Reply

Jeffrey February 18, 2012 at 11:54 am

Great trick, it is like Vlookup on STEROIDS!!! Thanks a million this is a fabulous site.

Reply

Mynda Treacy February 20, 2012 at 9:46 pm

:) Thanks, Jeffrey. Glad you like it.

Kind regards,

Mynda.

Reply

darren February 21, 2012 at 3:30 am

Thanks this works great can this be changed in any way to work
over more than one sheet instead of a table at all

Reply

Mynda Treacy February 21, 2012 at 8:08 pm

Hi Darren,

Yes you could modify it to work on multiple sheets. e.g.

=SUM(VLOOKUP(B3,Table1[[Name]:[Pay Rise 2006]],{4,5,6},FALSE),SUM(VLOOKUP(B3,Table2[[Name]:[Pay Rise 2006]],{4,5,6},FALSE)))

Where Table1 is on Sheet1 and Table2 is on Sheet2 and so on.

Note: Don’t forget to press CTRL+SHIFT+ENTER to enter it as an array formula.

Kind regards,

Mynda.

Reply

darren February 22, 2012 at 6:08 am

Thank you works 100% thank you again

Reply

Simon May 8, 2012 at 10:43 pm

Thanks, looks like this is what I am looking for but not getting the desired result!
Please help, what am I doing wrong…? I am working on a bigger spreadsheet but have made a small one to test to see if this works before using in the main one.
I am trying to look up a value in a cell (a person’s name) and want to know if it appears in the table. The person’s name is in cell B4 and the table is E4:G10 and I entered the following array formula (using crtl>shift>enter) {=ISNA(VLOOKUP(B4,E4:G10,{1,2,3},FALSE))}

It finds the name if it appears in the first column (ie E4:E10) but not if it is in any of the others (i.e F4:G10)

best wishes
Simon

Reply

Mynda Treacy May 9, 2012 at 8:40 pm

Hi Simon,

Since you don’t want to return a corresponding value once you find the name you can simply use the MATCH function with the IF function to tell you if the name exists in your table. Like this:

=IF(MATCH(B4,$E$4:$G$4,0),”Found”,”Not Found”)

This will return the text ‘Found’ or ‘Not Found’ depending on the outcome.

I hope that helps.

Kind regards,

Mynda.

Reply

Simon May 10, 2012 at 12:30 am

Hi Mynda

Thanks for getting back so quickly!

Had some success, but getting #N/A error

=IF(MATCH(B4,$E$4:$G$4,0),\Found\,\Not Found\)

B C D E F G
4 Sam Found Pete Sam Shaun
5 Shaun Amy Pete

Sam #N/A Pete Shaun
Shaun Amy Pete

Not sure why?

Regards
Simon

Reply

Simon May 10, 2012 at 12:35 am

Sorry that did not come out like I typed:
=IF(MATCH(B4,$E$4:$G$4,0),”Found”,”Not Found”)

If I type Sam into the table, it shows Found, if it is not in the list I get the #N/A

Regards
Simon

Reply

Mynda Treacy May 10, 2012 at 11:22 am

Hi Simon,

Ah, sorry. In my haste I didn’t check the ‘Not found’ scenario worked.

Here you go:

=IF(ISNA(MATCH(B4,$E$4:$G$4,0)),”Not Found”,IF(MATCH(B4,$E$4:$G$4,0),”Found”))

You may need to enter the double quotes in again because the display in italics on the web page and Excel doesn’t like them like that.

Kind regards,

Mynda.

Reply

Simon May 10, 2012 at 4:42 pm

Hi Mynda
That worked 100%, thanks I am very grateful to you!

Best wishes
Simon

Reply

Mynda Treacy May 10, 2012 at 8:15 pm

You’re welcome. Thanks for letting me know.

Kind regards,

Mynda.

Simon May 25, 2012 at 5:26 pm

Hi Mynda

I have a sheet and I want to check in a table on a if the person is trained, and if they are then the cell should show “SB”. This works fine except some cells get #N/A. Please would you tell me how to use the ISNA function in my formula to take care of this error?

=IF(VLOOKUP(M4,Lists!$H$2:$K$38,3,)=0,”",”SB”)

Thanks
Simon

Reply

Mynda Treacy May 25, 2012 at 8:31 pm

Hi Simon,

If you’ve got Excel 2007 or 2010 then you should use the new IFERROR function to handle #N/A errors. The old IF ISNA combination requires Excel to calculate the VLOOKUP twice, where as the new IFERROR is much more efficient.

=IFERROR(IF(VLOOKUP(M4,Lists!$H$2:$K$38,3,)=0,””,”SB”),”")

If you only have Excel 2003 you can use the IF ISNA like this:

=IF(ISNA(VLOOKUP(M4,Lists!$H$2:$K$38,3,)),”",IF(VLOOKUP(M4,Lists!$H$2:$K$38,3,)=0,”",”SB”))

Kind regards,

Mynda.

Reply

Simon May 28, 2012 at 10:55 pm

Hi Mynda
Thanks so much, I wasn’t sure about the nesting and this works a treat! I have version 2003 here at work, so used the second solution. The 2207 version is much more straight forward and I can use this on my home pc. Once again thank you.

Best wishes
Simon

Reply

Mynda Treacy May 29, 2012 at 9:43 am

Thanks, Simon. Glad it helped.

Kind regards,

Mynda.

Reply

Safieh August 17, 2012 at 6:32 am

Hello!
Thank you for your great site & info.!
I am trying to use “lookup” function in Excell 2007 to match data in three different files. The program says I have too many arguments in the formula I have written (below). Please let me know if there is a better way to do what I am looking for.
=IFERROR(LOOKUP(H2,’[2002LoadedNetwork.xlsx]2002LoadedNetwork’!$A$2:$FE$27744,21,FALSE),LOOKUP(G2,[highway.xlsx]highway!$A$2:$AR$30744,39,FALSE)
Thank you!
Have a great day.
Safieh

Reply

Mynda Treacy August 17, 2012 at 3:12 pm

Hi Safieh,

What do you mean by ‘match’ and what are you trying to return? A single result, a sum of multiple values?

Your formula above is performing two LOOKUP’s but you haven’t told it what to do the with the result of those two lookups. Do you want them both displayed in once cell, added together, averaged etc?

Please give me an example of what you are trying to do.

Kind regards,

Mynda.

Reply

Cam August 22, 2012 at 7:00 pm

I need help with my lookups if possible. I’ve tried everything, but i can’t wrap my head around the logic.
I need my formula to show lookup using two or more criteria.
I want my formula to show the maximum value in column A, where the corresponding value in column B =1.
E.g.
A B
1 1
2 0
6 0
2 1
3 1
12 0

I want formula to show number 3, as it is the max value in A where B = 1.

Reply

Mynda Treacy August 22, 2012 at 8:50 pm

Hi Cam,

You need the MAX IF Function…except there isn’t one :) , but you can create it using this array formula:

=MAX(IF((B1:B6=1)*(A1:A6),(A1:A6)))
Entered with CTRL+SHIFT+ENTER

You can read this tutorial on the MAX IF Function for an explanation of how it works.

Kind regards,

Mynda.

Reply

Tony Wong September 17, 2012 at 10:35 pm

This is a very useful tip. But I have another question. Do you have the vlookup formula to lookup a column has repeated labels and return their respective results?

Col A Col B
John $20
Paul $15
John $30
John $50

Want results to look like

Col A Col B
John $20
$30
$50

Reply

Mynda Treacy September 18, 2012 at 9:20 pm

Hi Tony,

I was asked the same question just the other day. Yes, you can do it, but not with VLOOKUP, but with this array formula:

=IFERROR(INDEX($A$1:$B$10,SMALL(IF($A$1:$A$10="John",ROW($A$1:$A$10)),ROW(1:1)),2),"")

You need to enter it by pressing CTRL+SHIFT+ENTER.

Then copy it down your column as many times as required.

I hope that helps.

Kind regards,

Mynda.

Reply

Subash September 27, 2012 at 2:26 am

Mynda,
You are doing a great service for us MS Excel users, and making our life simpler with the examples. Thank you and keep up the good work.
Good Luck

Reply

Mynda Treacy September 27, 2012 at 3:18 pm

Thanks, Subash :)

Reply

Hozy October 8, 2012 at 7:10 pm

Hi Mynda,

I do happen to land on ur website as soon as i search for some problem in google & yes it brings a smile on my face as i know, the problem would be a past now…

Now my problem is that i have to search for a number from 1 sheet & the same value is there in the other sheet in a cell but there are also few more values in that particular cell entered by alt+enter.

eg. in sheet1 i in A1 i have
A
1 Container No
2 OOLU8064879
3 OOLU8017711
4 CRSU9193709

where as in sheet 2 i have
A
1 “OOLU8017711
OOLU8831158
TCNU6627307″
2 “OOLU8064879
CAIU8293838″

So now if i have to lookup for OOLU8064879 in sheet 2 it doesnt show.
Is there any solution for this one? i am sure u vl suggest 1.

Reply

Mynda Treacy October 8, 2012 at 7:45 pm

Hi Hozy,

You can use wildcards to lookup text that is embedded within a cell.

For example:

=VLOOKUP(“*”&Sheet1!A2&”*”,Sheet2!$A$1:$A$2,1,FALSE)

You can read more on wildcards in Excel here.

Kind regards,

Mynda.

Reply

Hozy October 8, 2012 at 8:24 pm

Thanks a tonn Mynda…. you rightly understood the problem & solved it for me… highly appreciate ur help.

Reply

Mynda Treacy October 8, 2012 at 9:09 pm

:) you’re welcome. Thanks for taking the time to let me know it worked.

Reply

Allie October 12, 2012 at 5:57 am

I’m looking for a slightly modified version of this and hope you can help. Trying to find the cost of an item based on a part number. Parts come from three different suppliers, each with their own unique number, and these parts are broken out over three columns with the same cost for each (A2:A1360). See below.

Co1 Co2 Co3 Cost
K123 1 456 $.07
K789 2 123 $.50

On another sheet I have a quote which I want to compare to the data from the first sheet. I would like the formula to reference the part number from the quote (B4 on Sheet 2), look through the three part # columns for a match, and then return the corresponding cost (I4 Sheet 2). Is this possible? I’ve tried several formulas but continue to get a #N/A error.

Thanks.

Reply

Mynda Treacy October 12, 2012 at 11:04 am

Hi Allie,

I’d use the SUMPRODUCT function for this:

=SUMPRODUCT((Sheet1!$D$2:$D$3)*(Sheet1!$A$2:$D$3=B4))

Using your example data above; where Sheet1!$D$2:$D$3 contains the cost, Sheet1!$A$2:$C$3 are columns 1,2 and 3 (above) and B4 is the value on Sheet2 that you’re looking up.

Kind regards,

Mynda.

Reply

Allie October 16, 2012 at 6:01 am

That worked prefectly. Thank you!

Reply

Scott November 17, 2012 at 1:51 am

Hello, I really need this formula to work as it would save me so much time but it doesn’t seem to be happening for me. I want to use a regular data range from another tab, but when i do this the value that it returns is just the first cell number in the { } section. So if section was {11,12,13} and row 11 = 100 row 12 = 150 and row 13 = 50. It only returns the value in row 11 (the 100) instead of adding them up (100+150+50 = 300 which is the value i want it to return.

Here is my formula i currently have in:
=SUM(VLOOKUP(A434,’BU1 Secured Rev’!$A$4:$M$1624,{11,12,13},FALSE))

Reply

Mynda Treacy November 17, 2012 at 1:48 pm

Hi Scott,

Did you enter your formula as an array formula? That is did you press CTRL, SHIFT and ENTER at the same time? To know if you’ve entered it correctly you can check the formula bar to see that Excel has entered curly brackets at the beginning and end of the formula.

Kind regards,

Mynda.

Reply

Clint Caban November 27, 2012 at 4:37 am

Hi Mynda, I am working with your instructions ” Excel VLOOKUP to the left using CHOOSE” and it is working fine as long as I am looking up in only one column. However I need to have it work comparing two columns . CanI sqeeze in a second CHOOSE string?
My issue is that the formula will return the first chosen value that it finds in column E (in your your Example) when the one I want is, say, the second which is matched up with a different partner value in the next column. Any help would be appreciated.

Thanks,

Clint

Reply

Mynda Treacy November 29, 2012 at 7:42 pm

Hi Clint,

I think what you are after is this VLOOKUP with multiple values formula paired with the CHOOSE function. So you need to concatenate the two columns you want it to find a match for.

If you need more help please send me your example file via the help desk.

Kind regards,

Mynda.

Reply

Nitin Shinde December 2, 2012 at 3:57 pm

Dear Sir/Madam,

Please solve my below one problem
eg:
column1 column2
mango 5
apple 8
mango 2
guava 3
mango 8

if i want mangoes details then answer should be as below by using only formula:

mango 5
mango 2
mango 8

i know it will get ans by using pivot table…. but i need to get by any formula.

Please revert me at the earliest

Thanks
Nitin Shinde

Reply

Mynda Treacy December 3, 2012 at 9:11 pm

Hi Nitin,

It’s a bit complicated so I’ve attached an example Excel file for you.

Kind regards,

Mynda.

Reply

Frank Sanchez December 5, 2012 at 12:32 am

Hi Mynda,
I’m having hard times and can’t figure this one out. I have created a drop down box with a list of names, underneath that cell I created a VLOOKUP for the DOB and under that one another one with an ID #. It does what is supposed to do. But when I copy those cells underneath those to do the same thing the VLOOKUP gives me another rage and not the same range as the previous block of cells. How can I make it do the same?

Reply

Mynda Treacy December 5, 2012 at 8:34 am

Hi Frank,

It sounds like you haven’t made the VLOOKUP range absolute.

Kind regards,

Mynda.

Reply

Frank Sanchez December 6, 2012 at 11:25 pm

Wow! you are good. Works perfectly!

Thank You,

Reply

Mynda Treacy December 7, 2012 at 9:59 am

:) Cheers, Frank.

Reply

Dustin December 12, 2012 at 12:52 am

I have the following formula in cell B1, and it returns the sheet name that the value in cell A1 is located
{=IF(A1=”",”",INDEX(Mysheets, MATCH(1, COUNTIF(INDIRECT(“‘” & Mysheets &”‘!A:A”), A1), 0)))}
And the following formula in C1 creates a link to the cell in the respective sheet
=IF($A1=0,”",HYPERLINK(“#”&CELL(“address”,INDEX(INDIRECT(“‘”&B1&”‘”&”!B:B”),MATCH(A1,INDIRECT(“‘”&B1&”‘”&”!A:A”),0))), “Link”))
What I would like to be able to do is merge and center cell A1 with A2, and have these same formulas in B2, and C2, but find the first AND second occurrences of value in cell A1 throughout the workbook and in cell c1 there is a link to the first occurrence, and in cell c2 there will be a link to the second occurrence

Reply

Dustin December 12, 2012 at 8:28 am

I worked around it, by putting an extra column instead of two rows. The extra column instead of searching the named range Mysheets, I made a list of sheets in reverse order, and named a new range Backwards, so it is the first occurence that it finds in D1, but it searched the sheets in reverse order.

Reply

Mynda Treacy December 12, 2012 at 9:15 am

:) very clever, Dustin.

I noticed in your formulas you are referencing whole columns e.g. A:A and B:B. If you find your workbook starts to slow down on calculation you might want to specify a smaller range e.g. A1:A10000 or less if you can. Referencing whole columns, particularly in array formulas, can be the death of your workbook!

Kind regards,

Mynda.

Reply

Sam December 18, 2012 at 2:38 pm

Hi there and thanks for the tips. I’ve been able to follow the vlookup and the sum vlookup and create my own examples, but what I really need is some sort of combination of a lookup, an array and a horizontal display result (I think).

Okay in my example I have a table, called pricelist and its in cells N15 to Q19:

pen 1.5 3 4
eraser 2 3 4
paper 1.7 3 4
pen 1.7 3 4
paper clip 3 3 4

the point being the “lookup” item eg pen appears in several rows.
Now when I look it up with a standard lookup in column 2 I get the answer 1.5 (correct)
When I look it up with a sum of all columns I get 8.5 (correct).

What I want it to do is look up pen but display the individual results in seperate cells, horizontally (or vertically just in case)

so the answer would look like
Pen 1.5, 1.7

I’m using the table to look up phone numbers (for an IT project) and their corresponding routing info, problem is the numbers appear in the table more than once.

This is the mess that I tried….no laughing please never heard of a macro let alone an array until an hour ago…..but learning how to name a range was very handy. :-) I cribbed it from one of your examples

=IFERROR(INDEX(pricelist,SMALL(IF(pricelist=”pen”,ROW(pricelist)),ROW(1:1)),2),”")

Using outlook 2010 and sorry to be difficult but please paste formula answer onto the site, I’ve tried to download some of your other example links to get more ideas and they kept freezing and crashing.

thanks very much for the help, appreciate it.

Sam

Reply

Mynda Treacy December 18, 2012 at 8:44 pm

Hi Sam,

There’s an example of lookup and returning multiple values here.

If you’re downloading the workbooks you need to make sure they’re being saved as the correct file type. Internet Explorer changes file extensions to .zip and you end up with a load of nonsense. To check the file type hover your mouse over the link, this will display the file name and extension in the bottom right or left of your browser window. Then when you save the file type of the .zip with .xlsx or .xlsm accordingly.

Kind regards,

Mynda.

Reply

Pitz December 19, 2012 at 6:39 am

Hi Mynda,
I am working on a project and would really need your help. Basically, I have a table of list of company names and each company have say, 5 owners. What I was trying to do is to put the company name in a cell through data validation and once you choose/select a certain company in your drop down menu, you should have the list of owners of that company to be automatically shown on the cells below the company names. Is there a way to do this?

Reply

Mynda Treacy December 19, 2012 at 8:54 am

Hi Pitz,

Yes, indeed you can do that. Here is a tutorial on lookup and return multiple values.

Kind regards,

Mynda.

Reply

chuck December 28, 2012 at 4:55 am

Greta site; I’ve looked at numerous tips & tricks — so many things I didn’t know!

However, I can’t seem to locate anything (at least that I can understand) that can help me with a particular excel problem I’m having. I’ve been on the internet help boards but still have no replies. Do you have anyone that might be willing to offer some guidance on an excel problem? I work for a career college and I’m having trouble with a solution on course scheduling.

thanks,
Chuck

Reply

Mynda Treacy January 1, 2013 at 8:17 pm

Hi Chuck,

If you’d like to send me your Excel file containing your problem by raising a ticket on the help desk I’ll do my best to help you out.

Kind regards,

Mynda.

Reply

Renat January 8, 2013 at 5:02 pm

Hi Mynda,
I have a question about how vlookup many cells in one column without “=concatenate” function.
For example:
A column; B column
DDD Yes
AAA Yes
CCC No
EEE Yes
RRR Yes

i want create formula in C2 cell which concatenate texts in A column if in B column indicated “Yes”. Result will be shown how (DDD, AAA, EEE, RRR).

Thanks in advanced.

Reply

Mynda Treacy January 8, 2013 at 8:31 pm

Hi Renat,

="("&IF(B2="Yes",A2&", ","")&IF(B3="Yes",A3&", ","")&IF(B4="Yes",A4&", ","")&IF(B5="Yes",A5&", ","")&IF(B6="Yes",A6,"")&")"

Kind regards,

Mynda.

Reply

Renat January 8, 2013 at 11:25 pm

Dear Mynda,
the cells count is equal 10000 – 15000. With Vlookup function cannot concatenate many specific cells.

Thanks in advance.

Reply

Mynda Treacy January 9, 2013 at 3:02 pm

Hi Renat,

I’m confused…are you saying you have up to 15000 rows of data and you want to display the text from column A where column B is ‘Yes’ in one cell?

If I’ve misunderstood perhaps the best option is to email me the file via the help desk with a clear explanation of what you want and where.

Kind regards,

Mynda.

Reply

Kamala January 11, 2013 at 9:05 pm

Hi thanks for your valuable tips its really worth considering these help.

Just wanted to bring it to your notice that Vlookup multiple value & vlookup Multiple coloum contains same working. Could you pls check

Thanks

Reply

Mynda Treacy January 11, 2013 at 9:11 pm

Hi Kamala,

Yes, it’s the same tutorial. Some people refer to it as looking up multiple values and some refer to it as looking up multiple columns. So we have listed the same tutorial under both descriptions.

Sorry for any confusion.

Kind regards,

Mynda.

Reply

Ansh February 14, 2013 at 2:59 am

Dear Mynda,

I am working on 2 sheets within the same excel sheet. In master sheet there are 3 columns A, B, C. In the Child sheet, A and B are calculated based on certain parameters. Now, I need to map the value of A & B from the child sheet and get the Value of C from the Master sheet. Please guide me to do the same.

For example A=5,00,000 and B=50% in child sheet. In master sheet, I need to match Value of A and B together and get the value of C, which is in the master sheet to my child sheet.

AND

I need to round up 2 digits number in the nearest 10 multiples and 3 digits number to the nearest 3 digits multiple. How is it possible within the same formulae.

For example if the value is 76, it should come 80 and if the value is 176, it should come 200. It needs to be done within the same formulae.

Thank you in advance and please suggest me at the earliest. It is urgent :-)

Reply

Carlo Estopia February 15, 2013 at 2:16 pm

Hi Ansh,

Here’s the overall formula. Don’t be overwhelmed. Just follow the lead. It’s all about copying the formulas below to the
Child Sheet.

THE OVERALL FORMULA TO C5 of the Child Sheet.

=IF(LEN(myVlookup(A5&B5,Ansh!$A$2:$C$16,3))< =2,IF(INT(RIGHT(myVlookup(A5&B5,Ansh!$A$2:$C$16,3),1))>5,myVlookup(A5&B5,Ansh!$A$2:$C$16,3)-RIGHT(myVlookup(A5&B5,Ansh!$A$2:$C$16,3),1)+10,myVlookup(A5&B5,Ansh!$A$2:$C$16,3)),IF(INT(RIGHT(myVlookup(A5&B5,Ansh!$A$2:$C$16,3),(LEN(myVlookup(A5&B5,Ansh!$A$2:$C$16,3))-1)))>50,myVlookup(A5&B5,Ansh!$A$2:$C$16,3)-RIGHT(myVlookup(A5&B5,Ansh!$A$2:$C$16,3),(LEN(myVlookup(A5&B5,Ansh!$A$2:$C$16,3))-1))+100,myVlookup(A5&B5,Ansh!$A$2:$C$16,3)))

Assumptions:
Ansh is the MasterSheet
Child is the ChildSheet

Data:
Ansh
—A—– –B–
2 5000000…50%

Child
—A—– –B—– —C—
1 1000000…50%…
2 2000000…50%…
3 3000000…50%…
4 4000000…50%…
5 5000000…50%… The Complete Formula as tested

Formula Broken Down: with their isolated results
1) The VBA function:Copy to D5 of Child

 myVlookup(A5&B5,Ansh!$A$2:$C$16,3) 


You MUST do this:

1 ALT+F11 (Brings the VBE WINDOW)
2 Go to Insert, Add Module (note: not Class Module)
3 Paste this Code in the Module

Function MyVLookup(LookUpValue As String, TBLArray As Range, Col_Return As Integer) As Variant
    Dim r As Long
    MyVLookup = ""
    Dim str As String
    If LookUpValue = "" Then Exit Function
    For r = 1 To TBLArray.Rows.Count
        str = TBLArray.Cells(r, 1).Value & TBLArray.Cells(r, 2).Value
        If TBLArray.Cells(r, 1).Value & TBLArray.Cells(r, 2).Value Like LookUpValue & "*" Then
            MyVLookup = TBLArray(r, Col_Return).Value
            Exit Function
        Else
        End If
    Next
    MyVLookup = "NoMatch"
End Function

2 THE EXCEL FORMULA FOR ROUNDING UP: COPY TO E5 of Child

=IF(LEN(D5)< =2,IF(INT(RIGHT(D5,1))>5,D5-RIGHT(D5,1)+10,D5),IF(INT(RIGHT(D5,(LEN(D5)-1)))>50,D5-RIGHT(D5,(LEN(D5)-1))+100,D5))

Child
—A—– –B—– —C————————— —-D—- —–E—————————–
1 1000000…50%…
2 2000000…50%…
3 3000000…50%…
4 4000000…50%…
5 5000000…50%… The Complete Formula as tested..MyVLookup..THE EXCEL FORMULA FOR ROUNDING UP.

Cheers.

CarloE

Reply

Nilofar kazi February 20, 2013 at 1:53 am

Hi Mynda,

i have 1 query.. if Brian is twice or thrice in column “A” and there is value only in “C”.. so can i get total qty for this?

i tried but i couldn’t get it… :(

Reply

Mynda Treacy February 20, 2013 at 12:12 pm

Hi Nilofar,

Brian is only once in column A. You can download the Excel workbook here and see the behind the scenes workings.

Note: this is a .xlsx file. Please ensure your browser doesn’t change the file extension to a .zip.

Kind regards,

Mynda.

Reply

nilofar February 21, 2013 at 10:12 pm

Thanks for file . :)

see below Eg. for my question.

mango-5
banana-10
apple-15
mango-10
apple-5

so i want lookup result like mango=15
apple=20

is it possible ..?

regards,
Nilofar

Reply

Carlo Estopia February 21, 2013 at 11:48 pm

Hi Nilofar,

    A      B
1 mango	   5		=SUMIF($A$1:$A$5,A1,$B$1:$B$5) or =SUMIF($A$1:$A$5,"mango",$B$1:$B$5) result:15
2 banana  10		
3 apple	  15		=SUMIF($A$1:$A$5,A3,$B$1:$B$5) or =SUMIF($A$1:$A$5,"apple",$B$1:$B$5) result:20
4 mango	  10		
5 apple	   5		

Read more: SUMIF

Cheers.

Carlo

Reply

Jess March 20, 2013 at 9:42 am

Hi,

I am having a little bit of difficulty with using the VLOOKUP, I want to be able to add up my columns, then divide them for an average between all 7. So entering data into a secondary sheet though when that particular item is selected from a drop down box, i want the values represented in the columns to add up and then my divided in order to give me the average number between them.

It has been wrecking my brain, and I am really hoping for a simple explanation.

=(VLOOKUP([@[Mob '#]],’Daily Feed Intake per Hd’!A3:J19,{sum(3,4,5,6,7,8,9,10},FALSE))/7

I really hope you can shed light on this.

Thanks, Jess

Reply

Carlo Estopia March 21, 2013 at 1:35 am

Hi Jess,

You might as well send a file via help desk.

Cheers.

CarloE

Reply

Sorin March 20, 2013 at 10:36 pm

Hi,
Thank you Mynda. :-) Very, very useful! :-)
Great job.

Sorin.

Reply

Carlo Estopia March 21, 2013 at 1:38 am

Hi Sorin,

On Behalf of Mynda,
You’re welcome!

Cheers.

CarloE

Reply

Damien March 27, 2013 at 7:24 pm

Hi,

I have a lookup that has me stumped. I have read through all your other posts and I cant seem to get a vlookup to do what I need for me to manage my household budget.

Now I have done an extract of my bank statement to excel and added a new column where I have catagorised each item, e.g. Mortgage, Petrol, Insurance, Child Care etc.

Now from another sheet what I want to be able to do is say take all the items for the month catagorised as say Petrol and put the sum of these values into my budget tab

Date: Description: $: Category:
Col A: Col B: Col C: Col D:
1/3/13, Description x, 20, Petrol
5/3/13, Description x, 20, Mortgage
9/3/13, Description x, 50, Petrol
19/3/13, Description x, 50, Petrol
23/3/13, Description x, 25, Child Care
30/3/13, Description x, 25, Insurance

So what I want returned to my cell is 120 for petrol for the month of March.

Any ideas?

Reply

Carlo Estopia March 28, 2013 at 2:56 pm

Hi Damien,

Try this formula and assume the data below:

=SUMIFS(C2:C7,D2:D7,"=Petrol",A2:A7,">=3/1/2013",A2:A7,"< =3/31/2013")

Data:

  A                  B          C         D
3/1/2013	Description x	20	Petrol
3/2/2013	Description x	20	Mortgage
3/3/2013	Description x	50	petrol
3/4/2013	Description x	50	Petrol
3/5/2013	Description x	25	Child Care
3/6/2013	Description x	25	Insurance

Take note of the date format. It will help. Just be consistent with it.

Cheers,

Carlo

PS: SUMIFS

Reply

joseph burbea March 29, 2013 at 7:47 am

Whenever I think that I found out everything there is to know about the function vlookup I am made to realize that there are more to know

Reply

Carlo Estopia March 30, 2013 at 10:39 pm

Hi Joseph,

Just take what you need and
be on your way… enjoy.

Cheers.

CarloE

Reply

juan April 19, 2013 at 4:46 pm

everything i wanted in excel is here… thanks!

Reply

Carlo Estopia April 19, 2013 at 7:13 pm

Juan Cheers on behalf of Mynda. :)

Reply

Kelly April 24, 2013 at 5:04 am

Great website. Is there a way to get the Vlookup to sum different rows instead of columns? Right now it is returning the first result and not taking into account the other matching results.

Reply

Mynda Treacy April 25, 2013 at 8:15 pm

Hi Kelly,

You need the SUMIF Function for this.

Kind regards,

Mynda.

Reply

gautam sanyal May 8, 2013 at 11:17 pm

your excel lessons are very useful and easy to understand.

Reply

Mynda Treacy May 9, 2013 at 7:14 am

Thanks, Gautam :)

Reply

Shamsuddin Kadri May 21, 2013 at 11:02 pm

Dear Mynda,

You are doing gr8 work. Your theme is so easy and easy to understand. This is very much help us who I want. So many many thanx and glad to use your website.

Reply

Mynda Treacy May 22, 2013 at 2:10 pm

Cheers, Shamsuddin :)

Reply

kamal May 18, 2013 at 12:36 am

thanks for sharing this great stuff. Ordinarly, we would ve use sum (vloop1,vlookup2,vlookup3). But the stuff given by u, is great use of array and smart also.

Reply

Mynda Treacy May 18, 2013 at 6:37 pm

Thanks, Kamal. Great alternative to use SUM with nested VLOOKUP’s :)

Reply

Sattam May 20, 2013 at 10:09 pm

Thanks Mynda, while this certainly helps in many situations, I have unable to find a solution (without using VBA) for one of my req, which is, to do exactly what vlookup do, but using multiple columns. While sumproduct and countif most certainly covers all required scenarios, but returning a string value in column C nased on whether colA (string match) and ColB (string match) have a certain value or not could not be achieved. your help with the same (without VBA) will be highly appreciated.
Regards,
Sattam

Reply

Mynda Treacy May 20, 2013 at 10:33 pm

Hi Sattam,

I’d be happy to take a look at your file. You can send it here. Please clearly explain what you’re trying to do so it’s quick for me to understand and help you.

Cheers,

Mynda.

Reply

Erich May 25, 2013 at 11:55 am

Mynda, Thanks very much for this incredibly informative tip, I’ve learned so much from reading your initial posting and subsequent replies to comments! I have a challenge I hope you can help with; I’m trying to find unique text in a single column cell, then count occurrences of different unique text in the row of cells adjacent to that column cell. It seems a combination of COUNTIF(S) and VLOOKUP is called for, but I can’t seem to frame it quite properly. A pivot table works, but I’m summing the occurrences on a separate worksheet and can’t extract the sum from that pivot table. Can you help?

Thanks,
Erich

Reply

Mynda Treacy May 25, 2013 at 12:05 pm

Hi Erich,

Thanks for your kind words :)

It’d be easiest for me to help you if you can send me your Excel file so I can see exactly what you’re working with. You can send it via the help desk.

Please also provide clear instructions on what you’re trying to find and count.

Cheers,

Mynda.

Reply

Erich May 25, 2013 at 1:48 pm

Mynda,

Thanks so much for the speedy reply! I’ve sent the file and hope it clarifies what I’m looking for.

Best regards,
Erich

Reply

Mynda Treacy May 25, 2013 at 4:46 pm

Cheers, Erich.

Reply

Rose June 14, 2013 at 4:08 am

Using VLookup is great however, there will be instances where faculty teaches two or more classes with different FTE (Full Time Equivalent) percentages. Can VLookup return multiple values? If so, how can I incorporate it into the formula used?

My spreadsheet contains 4 tabs, Biological Sciences, CSS, Eng&Math, PhySci; each tab contains faculty name, Position, Qtr, year, teaching assignment, salary, and FTE

=IFERROR(VLOOKUP(A2,’Biological Sciences’!$A$1:$W$36,2 ,FALSE),IFERROR(VLOOKUP(VLookup!A2,’Biological Sciences’!$A$1:$W$53,2,FALSE),IFERROR(VLOOKUP(VLookup!A2,CSS!$A$1:$W$53,2,FALSE),IFERROR(VLOOKUP(VLookup!A2,’Eng & Math’!$A$1:$W$53,2,FALSE),IFERROR(VLOOKUP(VLookup!A2,PhySci!$A$1:$W$53,2,FALSE),”")))))

Thank you for your help!

Rose Cabrales

Reply

Mynda Treacy June 14, 2013 at 2:30 pm

Hi Rose,

VLOOKUP can SUM multiple values as it’s doing in the example in my post above but from the look of your formula it may not work. If you can please send me the workbook via the Help Desk I can give you a tailored solution.

Cheers,

Mynda.

Reply

Fanie June 24, 2013 at 5:24 pm

How do i change the columns to rows, if i would like to vlookup then sum all the results found in rows and not columns?

Reply

Mynda Treacy June 24, 2013 at 7:37 pm

Hi Fanie,

Just replace VLOOKUP with HLOOKUP.

Kind regards,

Mynda.

Reply

karron July 25, 2013 at 10:42 am

Thank you for the tips. But I need some assistance. Here goes….I need to pull data from one tab in excel into another but I only want it to pull certain info.

I have a drop box with the locations listed and another one with dates. What I need to able to is pull certain data off my data tab when I select a location and date. Any assistance would be greatly appreciated.

Reply

Mynda Treacy July 25, 2013 at 10:49 am

Hi Karron,

It sounds like you need to lookup and return multiple values? If so this method might work. Let me know if you get stuck.

Kind regards,

Mynda.

Reply

Karron July 25, 2013 at 12:22 pm

I am stuck. I was able to get the vlookup to pull the data in one of the cells but I can not figure out how to make it to where if I change the date that it pulls from another cell within the data page. Below is part of my formula. =VLOOKUP($B$1,DATA!$E$2:$AJ$2624,7,FALSE)

What I need to add is F1 (which is where the date is located) and the data tabet goes from d2 to aj2426. I have a HUGE data page.

Reply

Mynda Treacy July 25, 2013 at 12:38 pm

Hi Karron,

It’s best if you send me your workbook via the help desk as I don’t know what you mean by ‘What I need to add is F1 (which is where the date is located) and the data tabet’.

Cheers,

Mynda.

Reply

Ranga October 9, 2013 at 10:45 pm

Hi Mynda,

Thanks very much, you have handled the topic brilliantly, makes for easy learning,

regards
Ranga

Reply

Mynda Treacy October 10, 2013 at 7:36 am

Thanks, Ranga :) Glad you liked it.

Reply

Gyan October 16, 2013 at 4:14 am

Dear Mynda
In your first example, you explained
col_index_num: {4,5,6} – Usually the column index number will be just one column in your table, but because we want to reference 3 columns, (Pay Rise 2004, 2005 and 2006), we’ve used an array which houses the 3 columns we want to reference (the array is defined by the curly brackets).
Now my question is that if I have to take sum of large number of columns , say from column no 4 to column no 20, do i need to write {4,5,6,…,20} or is there some better way?

rgds
Gyan

Reply

Mynda Treacy October 16, 2013 at 7:47 am

Hi Gyan,

Instead of {4,5,6…} use COLUMN(D:T) then in the formula bar select the ‘COLUMN(D:T)’ in the formula and press F9. This will give you a list of numbers 4 to 20 like this:

{4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20}

Or if you don’t want the numbers listed you can enter the formula as an array formula with CTRL+SHIFT+ENTER.

Kind regards,

Mynda.

Reply

Al December 5, 2013 at 8:38 am

I have a spreadsheet that has employee numbers in column A and corresponding pay for that week in column D,E,F. I want to be able to find all Direct Labor (DL) employees by employee number in column A and then find all values in column D,E,F that correspond the DL employee and add the dollar value as a single value. As an example.. i have employee number 15, 27, 48 and 52 as DL employees. I want excel to lookup those numbers in column A and then go to column D,E,F and add all the dollar values from those specific employee numbers.

Reply

Catalin Bombea December 5, 2013 at 3:57 pm

Hi Al,
Can you please upload a sample workbook with detailed information of what are you trying to achieve? It’s hard to work on descriptions only, we can find a solution for you a lot faster if we have a file to work with, i’m sure you can understand that :)
You can use the Help Desk: http://www.myonlinetraininghub.com/helpdesk/
Thank you,
Catalin

Reply

Arun February 23, 2014 at 5:19 pm

hi,

How can we vlookup the total sheet ( columns in single vlookup)
as in.

1st colunm contains the common numbers in both the sheet but we have to vlookup 20columns from one sheet to another.

Kindly help.

Regards
Arun

Reply

Catalin Bombea February 24, 2014 at 2:38 pm

Hi Arun,
You have to use VLOOKUP for all columns:
Assuming you have the identifier in column A, in column B put this formula: =VLOOKUP(A2,Sheet1!A2:L100,2). This will get you the data from column B, sheet1. This one, placed in column C: =VLOOKUP(A2,Sheet1!A2:L100,3) will get data from column C, Sheet1.
To fill this formula more easily, you can use this version, placed in B2, which will auto fill the column number:
=VLOOKUP($A2,Sheet1!$A$1:$L$100,Column()) Copy this to the right and down as needed.
Catalin

Reply

asim March 20, 2014 at 2:13 am

Dear Mynda
I like it your work hard for us.
Thanks

Reply

Mynda Treacy March 20, 2014 at 1:55 pm

Thank you, Asim :-)

Reply

Previous post:

Next post: