How to Vlookup to Return the SUM of Two or More Columns in Excel

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.

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.

vlookup return sum of multiple values1gif

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

vlookup return sum of multiple values2

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.
vlookup return sum of multiple values3

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

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],…)…
Related Posts

VBA Macro For VLOOKUP From Another Sheet
vba macro for vlookup from another sheet1

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you how to use VBA code to vlookup data ...

VLOOKUP From Another Sheet Not Working
vlookup from another sheet not working3

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you reasons why your VLOOKUP formula is not working ...

Fix #N/A Error For VLOOKUP From Another Sheet
vlookup from anther sheet not working1

This post will show you how to fix the #N/A error why it occurs when you extract values from another sheet using VLOOKUP function in Excel 2016,2013,2010 or other Excel versions. How can you correct a #N/A error in VLOOKUP ...

Get Employee Information by VLOOKUP

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. Today, in this article, we will show you the way to apply VLOOKUP to retrieve employee information. I hope this article will help you in ...

VLOOKUP with Two Lookup Tables

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. Today we will show you the application of VLOOKUP function when there are two lookup tables. EXAMPLE Table1 and table2 record the rates of Y2020 ...

VLOOKUP with Multiple Lookup Values

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match ...

VLOOKUP Data by Date

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match ...

VLOOKUP – Retrieve Data from Another Workbook
VLOOKUP - Retrieve Data from Another Workbook 1

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match ...

VLOOKUP – Retrieve Data from Another Worksheet
VLOOKUP - Retrieve Data from Another Worksheet 3

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match ...

Case Sensitive Lookup with SUMPRODUCT and EXACT

Today, we will show you how to use SUMPRODUCT and EXACT to perform a case sensitive exact match. In this article, we provide a simple example to calculate bonus for employees whose names are case-sensitive. If you meet similar scenarios ...

Sidebar