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.
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:
- lookup_value: B3 – This is the name we choose from the data validation list.
- 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.
- 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).
- [range_lookup]: FALSE – this simply instructs Excel to find an exact match for the
- 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.
Ahmed says
Thanks
dav says
Can you please post a link to the file?
Catalin Bombea says
Hi David,
There is no link to the file, you can apply the formula from this article on your table, just adjust the ranges, and don’t forget to confirm with Ctrl+Shift+Enter after editing the formula.
If you still have problems, upload your test file to our Help Desk and i will help you.
Cheers,
Catalin
dav says
Hi, Mynda.. Thank you for the help. But why do I need to enter shift-ctrl-enter for the formula to work?
Also, can you provide more tutorials on both pivot tables and vlookup? I’m trying to undderstand this when I apply for finance jobs.
Appreciate your help and looking forward to your reply.
Catalin Bombea says
Hi David,
CSE (Ctrl+Shift+Enter) is the way we tell excel that the formula is not a regular formula, it’s an array formula, check this article.
For pivot tables, Here are some articles: Pivot Tables
For Vlookup tutorials, try this google search in our website.
Cheers,
Catalin
dav says
Thank you soi much Mynda. Appreicate all the help!!!
Catalin Bombea says
You’re wellcome 🙂
Mike says
Fill in the assigned shift/job based on employee and date Time
EMPLOYEE date & time Dept/Job
DR 6/16/14 3:15 PM
EMPLOYEE Shift Start Date & Time Shift end date & time Dept/Job
AN 6/15/14 5:00 PM 6/15/14 11:00 PM PMOD – MRH-WEND
BR 6/15/14 8:00 AM 6/15/14 4:59 PM PEMB AMOD-WEND
Best Regards,
Michael F. Mini
Radiology Associates of Hollywood
9050 Pines Blvd, Suite 200
Pembroke Pines, FL. 33024
(954)437-4800, ext 2104
Catalin Bombea says
Hi Mike,
Try this file from our OneDrive Folder.
Catalin
Mike says
Thank you Catalin.
I am having trouble with the formula. When i modify the the time to match or when I copy the formula. I don’t see where the issue is.
Catalin Bombea says
Hi Mike,
Please use our Help Desk system to upload your file with changes you made. I hope you noticed that it’s an array formula that should be entered with Ctrl+Shift+Enter, not just enter after editing the formula.
Regards,
Catalin
Mike says
I have uploaded the file.
Catalin Bombea says
Hi Mike,
If you double click on cell H6, in the file you uploaded, you’ll notice coloured rectangles around cells F3 and G3, this means that the formula from H6 is refering to cells F3 and G3, not F6 and G6, which is obviously not right. You can simply drag down those coloured rectangles to F6 and G6 (G3 has 2 of these)
I also made this change to the formula:
Catalin
Michelle says
Very helpful, thank you Mynda.
Philip Treacy says
You’re welcome.
asim says
Dear Mynda
I like it your work hard for us.
Thanks
Mynda Treacy says
Thank you, Asim 🙂
Arun says
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
Catalin Bombea says
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
Al says
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.
Catalin Bombea says
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: https://www.myonlinetraininghub.com/helpdesk/
Thank you,
Catalin
Ranga says
Hi Mynda,
Thanks very much, you have handled the topic brilliantly, makes for easy learning,
regards
Ranga
Mynda Treacy says
Thanks, Ranga 🙂 Glad you liked it.
Gyan says
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
Mynda Treacy says
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.
karron says
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.
Mynda Treacy says
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.
Karron says
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.
Mynda Treacy says
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.
Fanie says
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?
Mynda Treacy says
Hi Fanie,
Just replace VLOOKUP with HLOOKUP.
Kind regards,
Mynda.
Rose says
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
Mynda Treacy says
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.
Erich says
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
Mynda Treacy says
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.
Erich says
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
Mynda Treacy says
Cheers, Erich.
Sattam says
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
Mynda Treacy says
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.
kamal says
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.
Mynda Treacy says
Thanks, Kamal. Great alternative to use SUM with nested VLOOKUP’s 🙂
gautam sanyal says
your excel lessons are very useful and easy to understand.
Mynda Treacy says
Thanks, Gautam 🙂
Shamsuddin Kadri says
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.
Mynda Treacy says
Cheers, Shamsuddin 🙂
juan says
everything i wanted in excel is here… thanks!
Carlo Estopia says
Juan Cheers on behalf of Mynda. 🙂
Kelly says
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.
Mynda Treacy says
Hi Kelly,
You need the SUMIF Function for this.
Kind regards,
Mynda.
joseph burbea says
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
Carlo Estopia says
Hi Joseph,
Just take what you need and
be on your way… enjoy.
Cheers.
CarloE
Damien says
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?
Carlo Estopia says
Hi Damien,
Try this formula and assume the data below:
Data:
Take note of the date format. It will help. Just be consistent with it.
Cheers,
Carlo
PS: SUMIFS
Sorin says
Hi,
Thank you Mynda. 🙂 Very, very useful! 🙂
Great job.
Sorin.
Carlo Estopia says
Hi Sorin,
On Behalf of Mynda,
You’re welcome!
Cheers.
CarloE
Jess says
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
Carlo Estopia says
Hi Jess,
You might as well send a file via help desk.
Cheers.
CarloE
Nilofar kazi says
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… 🙁
Mynda Treacy says
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.
nilofar says
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
Carlo Estopia says
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 5Read more: SUMIF
Cheers.
Carlo
Ansh says
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 🙂
Carlo Estopia says
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.
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
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 Function2 THE EXCEL FORMULA FOR ROUNDING UP: COPY TO E5 of Child
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
Kamala says
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
Mynda Treacy says
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.
Renat says
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.
Mynda Treacy says
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.
Renat says
Dear Mynda,
the cells count is equal 10000 – 15000. With Vlookup function cannot concatenate many specific cells.
Thanks in advance.
Mynda Treacy says
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.
chuck says
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
Mynda Treacy says
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.
Pitz says
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?
Mynda Treacy says
Hi Pitz,
Yes, indeed you can do that. Here is a tutorial on lookup and return multiple values.
Kind regards,
Mynda.
Sam says
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
Mynda Treacy says
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.
Dustin says
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
Dustin says
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.
Mynda Treacy says
🙂 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.
Frank Sanchez says
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?
Mynda Treacy says
Hi Frank,
It sounds like you haven’t made the VLOOKUP range absolute.
Kind regards,
Mynda.
Frank Sanchez says
Wow! you are good. Works perfectly!
Thank You,
Mynda Treacy says
🙂 Cheers, Frank.
Nitin Shinde says
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
Mynda Treacy says
Hi Nitin,
It’s a bit complicated so I’ve attached an example Excel file for you.
Kind regards,
Mynda.
Clint Caban says
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
Mynda Treacy says
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.
Scott says
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))
Mynda Treacy says
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.
Allie says
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.
Mynda Treacy says
Hi Allie,
I’d use the SUMPRODUCT function for this:
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.
Allie says
That worked prefectly. Thank you!
Hozy says
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.
Mynda Treacy says
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.
Hozy says
Thanks a tonn Mynda…. you rightly understood the problem & solved it for me… highly appreciate ur help.
Mynda Treacy says
🙂 you’re welcome. Thanks for taking the time to let me know it worked.
Subash says
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
Mynda Treacy says
Thanks, Subash 🙂
Tony Wong says
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
Mynda Treacy says
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:
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.
Jihad Aoun says
Hi Mynda,
I am also facing the same problem as Tony with multiple values to the same label.
Col A Col B
Plan1 Offer1
Plan2 Offer2
Plan1 Offer3
Plan1 Offer4
Want results to look like
Col A Col B
Plan1 Offer1
Offer3
Offer4
Do you have another formula please ??
Thanks and Best Regards,
Catalin Bombea says
Hi Aoun,
Why do you need another formula? The data you presented is in exactly same structure as Tony’s data, so his formula should work with your data too:
, confirmed with Ctrl+Shift+Enter, as it’s an array formula.
Let me know if you succeeded to apply the formula. If you need more help on this, you can upload a sample of your data on Help Desk
Catalin
Cam says
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.
Mynda Treacy says
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.
Safieh says
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
Mynda Treacy says
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.
Simon says
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
Mynda Treacy says
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.
Simon says
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
Mynda Treacy says
Thanks, Simon. Glad it helped.
Kind regards,
Mynda.
Simon says
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
Simon says
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
Mynda Treacy says
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.
Simon says
Hi Mynda
That worked 100%, thanks I am very grateful to you!
Best wishes
Simon
Mynda Treacy says
You’re welcome. Thanks for letting me know.
Kind regards,
Mynda.
Simon says
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
Mynda Treacy says
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.
darren says
Thanks this works great can this be changed in any way to work
over more than one sheet instead of a table at all
Mynda Treacy says
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.
darren says
Thank you works 100% thank you again
Jeffrey says
Great trick, it is like Vlookup on STEROIDS!!! Thanks a million this is a fabulous site.
Mynda Treacy says
🙂 Thanks, Jeffrey. Glad you like it.
Kind regards,
Mynda.
Rajesh Peter says
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!!!
Mynda Treacy says
Cheers. Glad you liked it.
Kind regards,
Mynda.
Wayne says
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!
Mynda Treacy says
Cheers, Wayne. Let me know if you’re still getting #REF! and I’ll take a look.
Kind regards,
Mynda
Keri Smith says
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!
Mynda Treacy says
Thanks Keri. I appreciate your feedback.
Kind regards,
Mynda.