# Extract Values from Several Columns [VLOOKUP Quick Tip]

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

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

### Special Thanks to

John for the tip. (Click on the name to see tip)

### Similar Tips

 3 Lookup Formula Challenges + 2 Jokes + 1 Link [VLOOKUP Week] Using Lookup Formulas with Excel Tables [Video]
 Written by Chandoo Tags: array formulas, downloads, Learn Excel, Microsoft Excel Formulas, quick tip, spreadsheets, vlookup, vlookup week Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

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

1. Vipul says:

Awesome!!

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

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

4. Mennen says:

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

5. Vipul says:

@Gregory Awesome again! Tx.

6. Sachin says:

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

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

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

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

Anand

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

• Ufoo says:

I love this formula

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

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

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

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

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

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

17. Joe says:

Thanks for the share Chandoo.......

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

19. Dibs says:

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

Thanks
Dibs

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

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

• Hui... says:

@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

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

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

24. Vish says:

Hi,

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

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

• Pranay A Patil says:

Same qeuary I have, Any help or resolution you got

• subrina roy says:

same issue I have plz help

26. Sandeep says:

Thank you!

27. gourav says:

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

using one vlookup formula for lookup value for multiple value.

 3 Lookup Formula Challenges + 2 Jokes + 1 Link [VLOOKUP Week] Using Lookup Formulas with Excel Tables [Video]