Extract Values from Several Columns [VLOOKUP Quick Tip]

Posted on November 8th, 2010 in Excel Howtos , Learn Excel - 24 comments

This article is part of our VLOOKUP Week. Read more.

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]

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]

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

VLOOKUP Week @ Chandoo.org - Learn tips on lookup formulas in Excel

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

24 Responses to “Extract Values from Several Columns [VLOOKUP Quick Tip]”

  1. Gregory says:

    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.

  2. Tristan says:

    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.

  3. Mennen says:

    wow! great post.
    i never thought this method of using vlookup is possible.

  4. Vipul says:

    @Gregory Awesome again! Tx.

  5. Sachin says:

    This is great. I was looking for this kind of solution for sometime…thanks for sharing..

  6. Justice says:

    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,

  7. Vipul says:

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

  8. Anand Tajpuriya says:

    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

  9. Tristan says:

    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

  10. MarkyB says:

    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

  11. Carlos says:

    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?

  12. Tony says:

    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

  13. SuSapta says:

    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.

  14. Krishna says:

    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.

  15. Krishna says:

    I got the answer for my problem mentioned above, By changing manual to automatic, in the calculation options of formula menu, , the problem sloved.

  16. Joe says:

    Thanks for the share Chandoo…….

  17. Dibyendu says:

    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

  18. Dibs says:

    Hi – could you please help me with my question, I am really looking forward for some support and solution here.

    Thanks
    Dibs

  19. Henry says:

    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?

  20. Jenny says:

    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!

Leave a Reply