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

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: http://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,

Read 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

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

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.