fbpx
Search
Close this search box.

Extract Values from Several Columns [VLOOKUP Quick Tip]

Share

Facebook
Twitter
LinkedIn
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

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

39 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

    • Naveen.TSS says:

      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

  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!

  21. Wendy says:

    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.

  22. radiostar says:

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

    • radiostar says:

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

  23. Vish says:

    Hi,

    The formula to retrieve the multiple data is not working in Google spread sheets. Can any one help me?

  24. Bharti says:

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

  25. Sandeep says:

    Thank you!

  26. gourav says:

    i want to use multiple columns result using and selecting muliple column,

    using one vlookup formula for lookup value for multiple value.

  27. kris says:

    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.

    • Hui... says:

      @Kris

      Ask the question in the Chandoo.org forums
      https://chandoo.org/forum/

      Attach a sample file to get a better, quicker answer

    • Chandoo says:

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

  28. Ric says:

    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

Leave a Reply