The** VLOOKUP** function is one of the most powerful, flexible, and extremely useful **Microsoft Excel functions** to search and retrieve values – either exactly matched values or the closest matched values – by looking up a corresponding value. But to achieve a certain result, utilizing only the **VLOOKUP** function is not enough sometimes. This article will show you how to use **the VLOOKUP function** with **the SUM function** to execute certain operations in Excel. Before diving into the methods, have a look at the overview image below.

**Table of Contents**hide

**Download Practice Template**

You can download the free practice Excel template from here and practice on your own.

**Introduction to VLOOKUP in Excel**

**VLOOKUP** stands for ‘**Vertical Lookup**‘. It is a function that makes Excel search for a certain value in a column, in order to return a value from a different column in the same row.

**Generic Formula:**

`=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])`

Here,

Arguments | Definition |
---|---|

lookup_value |
The value you are trying to match |

table_array |
The data range that you want to search your value |

col_index_num |
Corresponding column of the lookup_value |

range_lookup |
This is a Boolean value: TRUE or FALSE. FALSE (or 0) means exact match and TRUE (or 1) means approximate match. |

**6 Useful Methods to Utilize VLOOKUP with SUM Function in Excel**

In this section, we will learn how to utilize **VLOOKUP** and **SUM** functions in Excel together to generate certain results.

**1. VLOOKUP and SUM to Calculate Matching Values in Columns**

Consider the following dataset which consists of students’ names and their obtained marks on each course stored in different columns. What if you want to find out just a particular student’s total marks? To get that, you have to calculate numbers based on different columns.

Let’s figure out how to look in different columns and get the sum result of matching values in those columns using **VLOOKUP SUM** functions in Excel.

**Steps:**

- Select the name or the data that you want to find the result from the dataset and put the name or the data in another cell. (e.g. John in
**Cell E12**). - Click on another cell where you want the result to appear (e.g.
**Cell E13**). - In that cell, write the following formula,

`=SUM(VLOOKUP(E12,B5:G9,{1,2,3,4,5,6},FALSE))`

Where,

**E12** = John, the name that we stored as the lookup value

**B5:G9** = Data range to search the lookup value

**{1,2,3,4,5,6}** = Corresponding columns of the lookup values (columns that has John’s marks on each course stored)

**FALSE** = As we want an exact match, so we put the argument as **FALSE**.

- Press
**Ctrl + Shift + Enter**on your keyboard.

This process will give you the result that you required (John’s total marks are **350**, achieved by the summation of the marks of his Math, Physics, Chemistry, Biology, and English courses).

**Formula Breakdown:**

Let’s break down the formula to understand how we found John’s mark.

**VLOOKUP(E12,B5:G9,{1,2,3,4,5,6},FALSE)**-> looking for**E12**(John) in the**B5:G9**(array) and returning the exact corresponding columns values**({1,2,3,4,5,6}, FALSE)**.

**Output: 90,80,70,60,50** (which is exactly the marks John achieved on individual courses)

**SUM(VLOOKUP(E12,B5:G9,{1,2,3,4,5,6},FALSE))**-> becomes**SUM(90,80,70,60,50)**

**Output:** **350** (John’s total marks)

**2. VLOOKUP and SUM to Determine Matching Values in Rows**

Consider the following dataset which consists of students’ names and their obtained marks on each course stored in different columns. What if you want to find out just those particular students’ total marks who have retaken the exam? The dataset holds some students’ marks on each course divided into two rows declaring them as two exam types. To get that, you not only have to calculate numbers based on different columns but also must take multiple rows into consideration.

Let’s figure out how to look at different columns and rows and get the sum result of matching values in those columns and rows using **VLOOKUP SUM** functions in Excel.

**Steps:**

- Select a cell in the worksheet to put the name or the data that you want to find the result from the dataset later (in our case, it was
**Cell E13**). - Click on another cell where you want the result to appear (e.g.
**Cell E14**). - In that cell, write the following formula,

`=SUMPRODUCT((B5:B11=E13)*C5:G11)`

This process will give you the result that you required (Every student’s total marks with the retaken exam).

**Formula Breakdown:**

Let’s break down the formula to understand how we found students’ total marks with the retaken exams,

**B5:B11=E13**-> it looks for the match of the lookup value (e.g. John in**Cell E13**) throughout the array of Name column (**B5:B11**) and returns**TRUE**or**FALSE**based on the search.

**Therefore, Output:** {**TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE**}

As we got **TRUE** values so now we know that there are matched values in the dataset. It is not a constant value-extracting process. Because we can write any name from the dataset in that cell (**E13**) and the result will be auto-generated in the result cell (e.g. **E14**). (see the picture above)

**SUMPRODUCT((B5:B11=E13)*C5:G11)**-> becomes**SUMPRODUCT{TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}*(C5:G11)**which means,**the SUMPRODUCT function**then multiplies the**TRUE/FALSE**return value with the return array and produce the result of only for the**TRUE**values and pass it to the cell.**FALSE**values are actually canceling the unmatched data of the table array, leading to only the matched values appearing on the cell.

**Output:** **750** (John’s total marks with the retaken exam)

**3. Generating Values in Two Different Worksheets Using VLOOKUP and SUM Functions**

We have exam marks of students in the Excel worksheet named **Marksheet**.

And in the worksheet named** Result Sheet**, we want to have all the student’s individual total obtaining marks.

The steps to calculate values from another sheet to a working sheet are shown below,

**Steps:**

- First, select the cell beside the data or wherever in that worksheet you want the output (e.g. the cell beside the name John).
- In that cell, just put a simple
**VLOOKUP-SUM**formula that you have already known from the previous discussion; formula such as,

`=SUM(VLOOKUP(D5,B5:G9,{1,2,3,4,5,6},FALSE)`

But as this worksheet doesn’t have any data to be considered, so it will produce an error in the cell. So, all you have to do is, just place the pointer of your mouse before the array declaration in the formula (e.g. **B5:G9**), and select the other sheet that you want your values from.

It will auto-generate that sheet into your working sheet, hence all the data on that sheet will also be a property of the working sheet.

Now the formula becomes,

`=SUM(VLOOKUP(D5,Marksheet!B5:G9,{1,2,3,4,5,6},FALSE))`

- Press
**Enter**and you will get the desired result (e.g. John’s total marks is**350**, generated from the**Marksheet**worksheet)

- Drag the row down by
**Fill Handle**to apply the formula to the rest of the rows to get the results.

You will get the result of all the lookup data from another sheet of Excel in your working Excel sheet.

**Read more:** **How to Vlookup and Sum Across Multiple Sheets in Excel**

**4. Measuring Values across Multiple Worksheets Implementing VLOOKUP and SUM Functions**

Okay, now that you know how to look up and retrieve value from one worksheet and get the result in another worksheet in Excel, it’s time to learn how to do that in multiple worksheets.

Consider the following data where we have three different worksheets named **Math Sheet, Physics Sheet **and** Chemistry Sheet** where each course obtaining marks of individual student were stored.

And we want to know only the total mark of the students, not the individual. So we can retrieve that in our working sheet from all of those individual sheets. And the process is similar to the process discussed before.

To auto-generate the whole sheet just before the array declaration, you manually picked the sheet just by clicking on it right? So, here you will do exactly like that. The difference is before you just had to select one sheet, but this time you will **select multiple sheets multiple times** just before the array declaration of every dataset from the related worksheet.

- The formula will look like this,

`=SUM(VLOOKUP(B5,'Math Sheet'!B5:G9,{1,2,3,4,5,6},FALSE),VLOOKUP(B5,'Physics Sheet'!B5:G9,{1,2,3,4,5,6},FALSE),VLOOKUP(B5,'Chemistry Sheet'!B5:G9,{1,2,3,4,5,6},FALSE))`

- Press
**Enter**and you will get the desired result (e.g. John’s total marks is**240**, generated from the worksheets from the**Math Sheet, Physics Sheet, Chemistry Sheet**).

- Drag the row down by
**Fill Handle**to apply the formula to the rest of the rows to get the results.

You will get the result of all the lookup data from multiple sheets of Excel in your working Excel sheet.

**Similar Readings:**

**How to VLOOKUP with Multiple Conditions in Excel (2 Methods)****Combine SUMIF and VLOOKUP Excel (3 Quick Approaches)**

**5. Summing up Values Presented in Alternative Columns with VLOOKUP and SUM Functions**

Consider the following dataset which consists of students’ names and their obtained marks on each course stored in different columns. What if you want to find out just a particular student’s total marks based on some specific courses? To get that, you have to calculate numbers based on alternative columns.

Let’s figure out how to look in alternative columns and get the sum result of matching values in those columns using **VLOOKUP SUM** functions in Excel.

**Steps:**

- Select the name or the data that you want to find the result from the dataset and put the name or the data in another cell. (e.g. John in
**Cell E12**). - Click on another cell where you want the result to appear (e.g.
**Cell E13**). - In that cell, write the following formula,

`=SUM(VLOOKUP(E12,B5:G9,{2,5},FALSE))`

Where,

**E12**= John, the name that we stored as the lookup value**B5:G9**= Data range to search the lookup value**{2,5}**= Corresponding columns of the lookup values (columns that has John’s marks on only Math & Biology courses stored)**FALSE**= As we want an exact match, so we put the argument as**FALSE**.- Press
**Ctrl + Shift + Enter**on your keyboard.

This process will give you the result that you required (John achieved a total of **150** marks on **Math** and **Biology** courses).

**Formula Breakdown:**

Let’s break down the formula to understand how we found John’s total marks in the Math and Biology courses.

**VLOOKUP(E12,B5:G9,{2,5},FALSE)**-> looking for**E12**(John) in the**B5:G9**(array) and returning the exact corresponding columns values of Math and Biology**({2,5},FALSE)**.

**So, Output:** **90,60** (which is exactly the marks John achieved on Math and Biology)

**SUM(VLOOKUP(E12,B5:G9,{2,5},FALSE))**-> becomes**SUM(90,60)**

**Output:** **150** (John’s total marks on Math and Biology)

**6. Implementation of VLOOKUP and SUM Functions in Array**

Look at the following dataset, where we need to find out not only just the name of the customer but also the total purchase of the large quantity of product that the customer purchased.

And we will utilize** VLOOKUP SUM** functions in Excel to extract the result from this large set of arrays.

**Steps:**

- Select a cell in the worksheet to put the name or the data that you want to find the result from the dataset later (in our case, it was
**Cell J5**). - Click on another cell where you want the result to appear (e.g.
**Cell J6**). - In that cell, write the following formula,

`=SUM(VLOOKUP(F5:F9,B5:C9,2,FALSE)*G5:G9*(E5:E9=J5))`

This process will produce the customer’s name along with the total purchase.

**Formula Breakdown:**

Let’s break down the formula to understand how we found customer names and relevant purchases.

**VLOOKUP(F5:F9,B5:C9,2,FALSE)**-> it looks for the exact name (**FALSE**argument) of all the Products (**F5:F9**) from the second table, in the Product array (**B5:C9**) from the first table and returns the price of that product (column index**2**).

**Therefore, Output:** **700,1500,100,300,500**

**VLOOKUP(F5:F9,B5:C9,2,FALSE)*G5:G9**->**G5:G9**refers to the Quantity column of the dataset.

So,**VLOOKUP(F5:F9,B5:C9,2,FALSE)*G5:G9**becomes**{(700,1500,100,300,500)*(10,50,20,200,80)}**.

**Output:** **7000,75000,2000,60000,40000**

**E5:E9=J5**-> it looks for the match of the lookup value (e.g. John in**Cell J5**) throughout the array of Name column (**E5:E9**) and returns**TRUE**or**FALSE**based on the search.

**Lastly, Output:** **{TRUE;FALSE;FALSE;FALSE;FALSE}**

As we got **TRUE** values so now we know that there are matched values in the dataset. It is not a constant value extracting process. Because we can write any name from the dataset in that cell (**J5**) and the result will be auto-generated in the result cell (e.g. **J6**).

**VLOOKUP(F5:F9,B5:C9,2,FALSE)*G5:G9*(E5:E9=J5)**-> becomes**(7000,75000,2000,60000,40000)*({TRUE;FALSE;FALSE;FALSE;FALSE})**, it multiplies the**TRUE/FALSE**return value with the return array and produce the result only for the**TRUE**values and pass it to the cell.**FALSE**values are actually cancelling the unmatched data of the table array, leading to only the matched values appearing on the cell (**J6**), meaning, if you put the name John from the Name dataset (**E5:E9**) in the cell**J5**, it will only generate the total purchase (**7000**) of John, if you put name Roman, it will produce**75000**in the result cell (**J6**). (see the picture above)

**Subsequently, Output:** **7000,0,0,0,0**

**SUM(VLOOKUP(F5:F9,B5:C9,2,FALSE)*G5:G9*(E5:E9=J5))**-> becomes**SUM(7000)**

**Output:** **7000** (which is exactly the total purchase amount of John)

**Key Points You Must Keep in Mind**

- As the range of the data table array to search for the value is fixed, don’t forget to put the
**dollar ($)**sign in front of the cell reference number of the array table. - When working with array values, don’t forget to press
**Ctrl + Shift + Enter**on your keyboard while extracting results. Pressing only**Enter**will work only when you are using**Microsoft 365**. - After pressing
**Ctrl + Shift + Enter**, you will notice that the formula bar enclosed the formula in**curly braces {}**, declaring it as an array formula. Don’t type those**brackets {}**yourself, Excel automatically does this for you.

**Conclusion**

This article explained in detail how to use **VLOOKUP** and **SUM** functions in Excel. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.

I have a multi sheet spread sheet keeping track of job hours. I have used VLOOKUP in succession to sum all the hours on multiple sheets and it works great… Until it gets to a sheet that does not contain the lookup value. I have searched all over for my issue, and VLOOKUP may be the incorrect solution. I was wondering if I could rattle anyone’s brain to make this work.

I.E. I have 1 excel document with 52 tabs. Each tab is a work week starting from January so WW1 is all the hours FOR sed jobs I did for that week. “joes house 2 hours ; mikes house 3 hours”… WW2, WW3 etc… Until WW52.

This is the function I made to add hours together…

=SUM(VLOOKUP(O30,’WW29′!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,’WW30′!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,’WW31′!$A$7:$M$110,{13},FALSE)) And it works great. But when that job is finished it is not on (for example WW32 tab). Hence I get the #N/A error. so for example, as the previous one works great when I expand the formula to cover all 52 sheets… (EXAMPLE OF NEXT PAGE WIOTHOUT LOOKUP VALUE)

=SUM(VLOOKUP(O30,’WW29′!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,’WW30′!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,’WW31′!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,’WW32′!$A$7:$M$110,{13},FALSE)) I get the #N/A error because the job is not listed on WW32. But I may add hours to that on WW45.

Is there a way to make VLOOKUP skip a sheet that does not have the referenced value and continue summing it till the end? I apologize, this may be as clear as mud but I will clarify anything if need be.

I have also tried IFERROR. You can set IFERROR to return text or even blanks, but does not seem to cover summing. I’m looking for how to SUM multiple sheets when some of the sheets do not contain the lookup value. When using IFERROR function, instead of RETURNING #N/A it just returns “YOU’VE ENTERERED TOO MANY ARGUMENTS FOR THIS FUNCTION”…

=IFERROR(VLOOKUP(O30,’WW29′!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,’WW30′!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,’WW31′!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,’WW32′!$A$7:$M$110,{13},FALSE),””)

And that’s just 3 sheets.

Any help would be greatly appreciated.

Hi Joe!

You are right. It is really difficult to understand the problem from the comment.

So, I’ve a requested you for the problematic document. Please check your email.

Regards

Shamim