If you have ever wished that the VLOOKUP function could return the sum of two or more related columns, this trick will get you there.

## Objective

Before we get into the details, let’s be clear about our objective. We have some transactions that were exported from our accounting system as shown below.

We would like to write a formula that will look up any given TransID and return the sum of three columns, Amount, Shipping, and Tax. This is illustrated in the screenshot below, where we enter a TransID such as 10578 and Excel computes the Total (1,076.36 = 1,006 + 10 + 60.36).

Since this is essentially a lookup task, our first instinct is to use VLOOKUP. However, we know that VLOOKUP can only return one related value, not the sum of multiple related values.

One common workaround is to add a helper column to the data that sums the three columns and then use a VLOOKUP to return the value from the new helper column. However, whenever possible in practice, we prefer to work with the data as it comes so that the workbook is easy to update in future periods.

Another common workaround is to write a formula that adds up three VLOOKUP functions, such as the following.

=VLOOKUP(...) + VLOOKUP(...) + VLOOKUP(...)

However, such a formula can be difficult to update and maintain over time since any change to the arguments need to be made three times instead of once. The good news is that we can wrap a SUMPRODUCT function around a single VLOOKUP function. Let’s get started.

## SUMPRODUCT

When the SUMPRODUCT has a single argument, it behaves much like the SUM function because it simply returns the sum of the values. When the SUMPRODUCT has multiple arguments, it returns the sum of the product of its arguments. To accomplish our objective, we will use the SUMPRODUCT to return the sum of the values in a single argument.

Basically, we will wrap a SUMPRODUCT function around a VLOOKUP function that returns an array of values, specifically, the related values from multiple columns. The SUMPRODUCT will sum the values in the array returned by the VLOOKUP function. How do we convince the VLOOKUP function to return an array of values? That brings us to our next discussion.

*Technical Note: we need to use SUMPRODUCT instead of SUM because the SUMPRODUCT function is designed to work with arrays and will thus return the sum of the array returned by the VLOOKUP function. The SUM function would return the sum of the first array element only. However, you could use the SUM function and then array-enter it with Control+Shift+Enter if preferred.*

## Array of Values

We can ask the VLOOKUP function to return an array of values by enclosing the third argument in curly braces {}. This causes the VLOOKUP function to actually return an array that contains multiple elements. For example, if we wanted the VLOOKUP function to return the values from the 3rd, 4th, and 5th columns to an array with three elements, we could use the following formula.

=VLOOKUP(C6,B12:F18,{3,4,5},0)

**Where:**

**C6**is the lookup value**B12:F18**is the lookup range**{3,4,5}**are the column numbers that have the values we want returned, enclosed in curly braces to create the array of values**0**tells VLOOKUP to use exact match logic

This VLOOKUP function actually returns an array of values, and the array elements are 1006, 10, and 60.36. If you enter it into a single cell and press Enter, Excel will display only the first element. But, we don’t want to display the first element, we want to compute the sum of all array elements. So, let’s put it all together and bring it home.

*Technical note: you can confirm the VLOOKUP returns an array of values by array-entering the formula into three cells across at once (Ctrl+Shift+Enter). Excel will display the three array elements in the three cells.*

## SUMPRODUCT and VLOOKUP

Now that we understand that using the curly braces causes VLOOKUP to return an array of values, let’s add up the values in the returned array with the SUMPRODUCT function.

Here is a screenshot of our workbook.

The formula in C7 follows.

=SUMPRODUCT(VLOOKUP(C6,B12:F18,{3,4,5},0))

The SUMPRODUCT function returns the sum of the array elements returned by the VLOOKUP function. This is essentially the same as SUMPRODUCT({1006, 10, 60.36}), which returns 1076.36.

When we press Enter, we confirm that it works as expected…yes!

*Note: there is no need to array-enter this formula by using Control-Shift-Enter.*

And that my friend is one way to return the sum of two or more columns with VLOOKUP. If you have another approach, please share by posting a comment below…thanks!

## Additional Resources

That’s really clever! How did you run across the option of putting an array of columns into the VLOOKUP function?

Joe,

That technique came from a participant in a discussion during one of my Excel sessions…pretty cool!

There is so much to learn about Excel…I wish I had discovered that trick years ago 🙂

Thanks

Jeff

Excellent – thank you. Is there any way to replace the {3,4,5} part with a name? Like can I name a group of columns and reference them that way? Not finding anything about naming an array.

Hey Jon,

You can create a named formula, as in ={3,4,5}, and use that instead:)

Let me know if you need anymore help!

Kurt LeBlanc

Jeff, this is a really cool solution.

More to the point – you explain it clearly and succinctly in a way that is not only easy to follow, but helps to ‘stick’ it in the brain.

You’re clearly a gifted teacher.

Nick

Thanks 🙂

Will this similarly work with hlookup?

Correct! It works because SUMIFS() works horizontally as well as vertically.

Thanks for the useful information.. I would like to add multiple values which are in the same column (unlike the example here where the addition is along the same row)…How do I do that. Can you please suggest.?

Hey Srinkanth!

Absolutely, with HLOOKUP:) It is exactly the same logic fortunately, just with nth row as the third argument instead of column. If you transpose the table in the sample file and adjust the lookup formula to HLOOKUP(), you can see hat even the array works as expected:)

Let me know if you need any help with the setup,

Kurt LeBlanc

Hi Kurt,

My table area is A1:K30. My Lookup value matches with H1. And I want to add from H10 to H28. Instead of writing all the row numbers from 10 to 28 withing HLookup [ like – hlookup{10,11,12,13,…….,26,27,28} ] is there any other options?

I understand now:)

Unfortunately, you have to enter each of the individual numbers in the array…or you can create a named formula that refers to the array, and use that in your lookup.

Let me know if that is clear

Kurt LeBlanc

This post is to subscribe to follow-up emails (meant to do that earlier, but forgot)

Great…thanks!

Thanks this is a great post. However, what’s missing is the solution that sums up multiple values in one column. All the values I want to sum up are not in multiple columns but in the same column. How do I make this work? Thanks in advance

Hi Sam! Summing values in a single column is discussed in this post:

http://www.excel-university.com/multiple-condition-summing-in-excel-with-sumifs/

Hope it helps!

Thanks

Jeff