Extract Values from Several Columns [VLOOKUP Quick Tip]
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:
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:
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
 
 

Leave a Reply
3 Lookup Formula Challenges + 2 Jokes + 1 Link [VLOOKUP Week]  Using Lookup Formulas with Excel Tables [Video] 
24 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 multicell 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
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
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 lookup 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