Excel VLOOKUP Multiple Values

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.

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

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

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

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:

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

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.

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

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?

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.

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.

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?

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.

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

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.

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 🙂

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.

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?

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

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.

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.

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

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.

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!

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?

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.

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))

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.

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.

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.

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.

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

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

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.

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)

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!

Resources

Affiliate Program

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

✕

Hang On, Don't Go Just Yet.

As a thank you for visiting how would you like a10% Discount Code to use with any of my courses?