This post will guide you how to **lookup a value and then return the sum of two or more related columns in Excel** 2013/2016. How do I Use VLOOKUP function and SUM function to lookup a specific value and return the sum of all matches in columns in Excel.

Table of Contents

## 1. Vlookup to Return the Sum of Two or More Columns

Assuming that you have a list of data in range A1:D5, in which contains sales data for three months. And you want to look up a product named “excel” in this range, and then return the sum of three months or columns. How to do it. You can use the VLOOKUP function to look up values in the given range, but the problem is that this function returns only one related sales value. If you want the VLOOKUP function to return the values from the two or more columns, you need to put column numbers that you want returned into VLOOKUP function as its third argument. Then the VLOOKUP function will also return an array values. Last, you can use the sum function to calculate the total value of the returned array values from VLOOKUP function.

So you can write down an Excel Array formula based on the VLOOKUP function and the SUM function as below:

**=SUM(VLOOKUP("excel", A2:D5, {2,3,4}, FALSE))**

Type this formula into a blank cell and press **Ctrl + Shift +Enter** keys on your keyboard to change it as array formula. You would notice that the total sales value of product “excel” in three months has been calculated.

**Note:**

- “excel” is the lookup value
- A2:D5 is the lookup range
- {2,3,4} are the column numbers that have sales values you want to return, it should be enclosed in curly braces so that to create the array of values.

Let’s see how this formula works:

**= VLOOKUP(“excel”, $A$2:$D$5, {2,3,4}, FALSE)**

This formula returns an array result that contain three sales values of product “excel”.

You can also use another formula based on the SUMPRODUCT and VLOOKUP functions to achieve the same result. Like this:

**=SUMPRODUCT(VLOOKUP("excel", A2:D5, {2,3,4}, FALSE))**

Type this formula into a blank cell and press **Enter** key.

The SUMPRODUCT function will return the sum of the array elements returned by the VLOOKUP Function.

## 2. lookup and Return the SUM of Two or More Columns using VBA

Now, let’s explore the second method, where we’ll create a VBA User Defined Function to achieve the lookup and sum operation. This approach allows for greater flexibility and customization compared to traditional formulas.

Press ALT + F11 in Excel to open the VBA editor.

In the VBA editor window, go to the “Insert” menu and select “Module.”

Copy and paste the provided VBA code for the LookupAndSum function.

```
Function LookupAndSum(lookupValue As Variant, lookupRange As Range, sumRange As Range) As Double
Dim lookupRow As Long
Dim sumTotal As Double
Dim i As Long
' Initialize sumTotal
sumTotal = 0
' Loop through each cell in the lookup range
For i = 1 To lookupRange.Rows.Count
' Check if the current cell matches the lookup value
If lookupRange.Cells(i, 1).Value = lookupValue Then
' Get the corresponding row number
lookupRow = lookupRange.Cells(i, 1).Row
' Loop through each cell in the sum range and add to sumTotal
For Each cell In sumRange.Columns
sumTotal = sumTotal + Cells(lookupRow, cell.Column).Value
Next cell
' Exit loop after finding the first match
Exit For
End If
Next i
' Return the sumTotal
LookupAndSum = sumTotal
End Function
```

With this code, you can now input arguments into the LookupAndSum function.

**lookupValue:** The value to lookup.

**lookupRange: **The range where the lookup value will be searched.

**sumRange: **The range of columns where the sum will be calculated.

Close the VBA editor window.

Enter a formula in any cell where you want to display the sum of columns based on a lookup value.

`=LookupAndSum("excel", A2:A5, B2:D5)`

where “**excel**” is the lookup value, A2:A5 is the range to search for the lookup value, and B2:D5 is the range of columns where the sum will be calculated.

After entering the formula, press Enter to calculate and display the result.

By following these steps, you should be able to successfully run the VBA code and use the LookupAndSum function in your Excel workbook.

## 3. Video: lookup and Return the SUM of Two or More Columns

This Excel video tutorial, we’ll explore two methods to lookup and return the sum of two or more columns. We’ll start by utilizing an array formula combining the SUM and VLOOKUP functions, followed by creating a VBA User Defined Function.

## 4. Related Functions

- Excel VLOOKUP function

The Excel VLOOKUP function lookup a value in the first column of the table and return the value in the same row based on index_num position.The syntax of the VLOOKUP function is as below:= VLOOKUP (lookup_value, table_array, column_index_num,[range_lookup])…. - Excel SUM function

The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)… - Excel SUMPRODUCT function

The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:**=**SUMPRODUCT (array1,[array2],…)…

## Leave a Reply

You must be logged in to post a comment.