Situation
VLOOKUP is great for extracting information from a huge data table based on what you are looking for. But what if you need to extract more than one column of information? For eg. Lets say you have salesperson’s name in left most column, and monthly sales figures in next columns, one for each month. Now, you want to find the total sales made by a given sales person. How do you go about it?
Data:
![Data for this Example -Get Multiple Outputs [VLOOKUP Quick Tip]](https://chandoo.org/img/f/vw/vloookup-from-multiple-columns-data.png)
Solution
Simple. You can use an array in the column number field of VLOOKUP. So for eg. =SUM(VLOOKUP(value, list, {2,3,4},false)) will give you sum of values in columns 2,3 and 4. Of course you must press CTRL+SHIFT+Enter after typing the formula. See few more examples:
Examples:
![Data for this Example -Get Multiple Outputs [VLOOKUP Quick Tip]](https://chandoo.org/img/f/vw/vlookup-multi-column-examples.png)
Sample File
Download Example File – Get Multiple Outputs [VLOOKUP Quick Tip]
Special Thanks to
John for the tip. (Click on the name to see tip)
Similar Tips
39 Responses to “Extract Values from Several Columns [VLOOKUP Quick Tip]”
Awesome!!
The underlying formula =VLOOKUP(value, list, {2,3,4},false) will show the first three months of data for a sales person, but you have to select three cells (one row in three columns) and enter the formula as an Array (Ctrl+Shift+Enter). This is a multi-cell array formula.
Great post!
I did think of one quick improvement for the array constant. You could use the COLUMN function to return an appropriately sized array. For example, the average monthly sale for Jonathan would become:
{=AVERAGE(VLOOKUP("Jonathan",$B$5:$N$17,COLUMN($B$1:$M$1),FALSE))}
One problem I have with these array formulas is that they do not yield an error if entered incorrectly. So if you type in the average formula and hit Enter (instead of Ctrl+Shift+Enter) you get a number in the cell, it’s just not the average you were intending to calculate. I’d rather have an error message instead of a result that might throw me off somewhere else; just something to keep in mind.
wow! great post.
i never thought this method of using vlookup is possible.
@Gregory Awesome again! Tx.
This is great. I was looking for this kind of solution for sometime...thanks for sharing..
Will these formulas work on Excel 2003? I've tried putting them in as shown and I'm getting a text result. If I remove the array the correct answer is displayed in the "function argument" screen but only the value of the first match is shown in the formula result. Any help on this is greatly appreciated.
Thanks,
@Justice make sure to hit Ctrl+Shift+Enter after entering the formula. If you have copy pasted the formula, hit F2 in the cell and then Ctrl+Shift+Enter. All these work in 2003 as well.
Hi:
Has anybody solved the "homework" problem in the Sample File ? Total commission of Jackie?
If somebody has, please put the solution here and explain.
Thanks in advance,
Anand
Hi Anand,
Here is what I used:
=SUMPRODUCT(INDEX(C5:N17,MATCH("Jackie",$B$5:$B$17,0),0),$C$20:$N$20)
The quoted Jackie can be replaced with reference to c cell with that text, of course.
Thanks,
Tristan
I love this formula
Chandoo + others,
Great post - Quick question .........
Re. Question 2: Complete the below Table with total Quarterly sales. Is there a way of entering a formula other than :
{=SUM(VLOOKUP("john",$B$5:$N$17,{2,3,4},FALSE))} ie Is there a way of not having to enter "john", "josh" & "jamie" in Q17, R17 & S17 & enter one array formula for the three rows at the same time ?
Many Thanks
MarkyB
Hi MarkyB,
There is a solution, albeit a lengthy one and it's dynamic. Apply once and drag on to the rest of the cells.
={SUM(INDEX(OFFSET($C$5,0,3*(COUNTA(Q$16:Q16)-1),13,3),MATCH(Q$16,OFFSET($B$5,0,0,13),0),0))}
goes with ctrl+shift+enter
I'm trying to a vlookup formula from a pivot table, the value that is in the pivot table is not a value, so the vlookup is not find it. I don't want to modify the pivot table. So the question is there a way to change the pivot table range to a value?
I seem to have a problem with the above example using Excel 2010. The above sample file is a 1997 - 2003 format. Whenever I try to save the file, it indicates that using Excel 2010, there will be some compatibility problem, which presumably means that I am using excel 2010.
Anyway, the issue relates to question 2. I applies the same formula as in the question 1 to calculate the Q1 sales. ie =sum(vlookup("John",$B$5:$N$17,{5,6,7},false)). I was expecting the cell to calculate 35. However, I get the answer as 11. Mysteriously, it just returned the value for the month of April. I tried for John, Josh and Jamie for Q1 to Q4. Each time, the formula would return the value for only the first month of that quarter. Weird.
Is there something wrong with my excel setting?? It seems extremely odd
Tony,
You probably missed the Ctrl+Shift+Enter part and instead just hit enter after entering the formula.
Press F2 and then hit Ctrl+Shift+Enter and it should work.
I am using xl2010, my data sheet contains Vlookup formula, The problem I am facing is, the data does not refreshes in the vlookup unless i press F2function and enter. Please suggest if some settings has to be changed.
I got the answer for my problem mentioned above, By changing manual to automatic, in the calculation options of formula menu, , the problem sloved.
Thanks for the share Chandoo.......
Hi- can this be extended to one more level, say we have multiple row items for each of the Sales Person and followed by the Product they sold...e.g. product AAA, BBB, CCC etc.
Basically Column A have the name of the persons, Column B - Name of the Product and then month wise Sales figure going forward from Column C onwards, however Sales Persons are repeated in multiple rows against various Products etc. Is there any way to find out which was the last product sold by a Sales Person and the Amount of it. This can be daily or weekly or monthly data.
But idea is to find the last/latest sold Product by an Individual Sales Person and in which month and the value of it.
Your help is really appreciated
Hi - could you please help me with my question, I am really looking forward for some support and solution here.
Thanks
Dibs
@Dibs
Can you post your question at the Chandoo.org forums?
http://chandoo.org/forums/
How about making automated calculations involving a range of months, for example:
need Jonathan's sales for the last quarter, or last month, or last semester, or for year to date?
@Henry
Can you provide a sample file
Possibly make a post in the Forums
http://chandoo.org/forum/
As you can attach files there
Great post!
How can this be adapted to pick up average between two dates?
If I have name, start date and end date mentioned, how best to adapt the formula to automatically look-up the average between those two dates?
Thanks!
@Jenny
Can you please post the question to the Chandoo.org Forums with a sample file?
http://chandoo.org/forum/
do you want the average between the dates or the average of the two dates
If I have one sheet that in column A has a customer number, column B has First Name, column C has last name. The second workbook I have customer number. Is there a way to do a vlookup on the customer number and concatenate column B & C.
In other words, I need to put first and last name in the customer number on the second sheet.
My solution to Homework Question #1:
=SUMPRODUCT(VLOOKUP(B9,$B$5:$N$17,ROW(A2:A13),FALSE),VLOOKUP(B20,$B$20:$N$20,ROW(A2:A13),FALSE))
Enter as an array formula (CSE).
P.S.: First, you'll need to unmerge cells B19&B20 and type "Commission" (or whataever pleases you, really) in cell B20.
I'm sure there are more sophisticated formulas out there...
Correcting the formula, so that you can drag it down:
=SUMPRODUCT(VLOOKUP(B9,$B$5:$N$17,ROW(A$2:A$13),FALSE),VLOOKUP($B$20,$B$20:$N$20,ROW(A$2:A$13),FALSE))
Hi,
The formula to retrieve the multiple data is not working in Google spread sheets. Can any one help me?
I tried all recommended above like Press F2 and then hit Ctrl+Shift+Enter but still i am getting the data is of the first column only in all consequent columns also.
This is how the Formula shows in first column:
=VLOOKUP($CJ5,'[DA Research HRMS Data & Mappings 31st July 2016.xlsx]HRMS, Std hrs.'!$C:$CZ,{19,20,21,22,23,24,25,26,27,28,29},0)
this is how it shows in 2nd column post doing F2 and Ctrl+Shift+Enter:
=VLOOKUP($CJ5,'[DA Research HRMS Data & Mappings 31st July 2016.xlsx]HRMS, Std hrs.'!$C:$CZ,{19,20,21,22,23,24,25,26,27,28,29},0)
Please of anyone can help..
Same qeuary I have, Any help or resolution you got
same issue I have plz help
Thank you!
i want to use multiple columns result using and selecting muliple column,
using one vlookup formula for lookup value for multiple value.
i have sheet containing 30000 rows with 10 columns data .
i want to extract all rows with one column having matching part of the string.
@Kris
Ask the question in the Chandoo.org forums
https://chandoo.org/forum/
Attach a sample file to get a better, quicker answer
@Kris.. as Hui suggested, you can post sample data and your question in our forums. If you can have a go, try below instructions.
Assuming you are using new versions of Excel (365 or 2016)
1. Select your data and turn it in to a table (CTRL+T)
2. Go to Data > Get & Transform > From table
3. This loads your data to Power Query.
4. On the column you want to filter, just filter by "Contains" conidition
5. Close and load
Your filtered data comes back as a new table.
Is there a way that I can recall multiple items in a list based off of meeting other conditions (without making a drop down list)?
I would like each recalled value to appear in its own cell within a defined column.....
PLEASE HELP