To understand Vlookup with Sum in multiple rows, you must know where it will come in use.

Vlookup formulas in Google Sheets is capable of returning multiple values in three different ways. What are they?

1. Vlookup with one search key but two or more index columns.
   • In this case, the formula would return a multi-column (single row) output.
2. Vlookup with two ore more search keys and one index column.
   • Here the formula would return a multi-row (single column) output.
3. Vlookup with two or more search keys and two or more index columns.
   • Here in this Vlookup, the output would be a matrix (multiple columns and rows).

In the first two cases (point # 1 and 2), we can use the Sheets Sum function with Vlookup. For the last case, we can use MMULT as what we want to do is Vlookup with Sum in multiple rows.

Things to Do Before Start:

Are you a first-time Vlookup user in Google Sheets? Then you may please check the following two tutorials.

1. Vlookup and Hlookup Unearthed – Google Doc Spreadsheet.
2. Vlookup in Google Sheets – 10 Formula Variations, Tips, and Tricks.

If you are a Google Sheets user who has migrated from Excel, then you may find this Sheets Vs Excel comparison of Vlookup interesting – Comparison of Vlookup Formula in Excel and Google Sheets.

You can consider bookmarking it in your browser for reading later.

Sample Data:

We can use the same set of sample data for all three variations (please refer to point # 1, 2, and 3 above) of formula.

Read on to get the said different Vlookup sum formulas.

Sum Multi-Column Vlookup Output in Google Sheets

The above sales data shows the sales of 5 products over a period of 12 months. How to Vlookup the sales of “Product 3” for the second quarter (April, May, and June)?

The column indexes of the second quarter are 5, 6, and 7 (please note that the first column in the ‘range’ A1:M7 is numbered as index 1).

=ArrayFormula(vlookup("Product 3",A1:M7,{5,6,7},0))

The above Vlookup ArrayFormula would return the quantities 5, 6, and 3 as the output in three columns in the applied row.

If I apply the above Vlookup formula in cell O2, the output would be in the cells O2, P2, and Q2.

Simply wrap the above Vlookup array output with the Sum function. This way you can sum multi-column Vlookup output in Google Sheets.

=sum(ArrayFormula(vlookup("Product 3",A1:M7,{5,6,7},0)))

If you want, you can replace the criterion “Product 3” with a variable also. See that formula and the final output on the image.

Sum Multi-Row Vlookup Output in Google Sheets

When there are two or more search keys in rows and only one index column, then the Vlookup will return a multi-row output.

To sum that output, here also use the Sum function with Vlookup. The formula to sum the above multi-row Vlookup output is as follows.

=sum(ArrayFormula(vlookup(N2:N3,A1:M7,2,0)))

MMULT to Sum Multi-Row and Column (Matrix) Vlookup Output

Think about a ‘combination’ of the above two Vlookup formulas without the Sum part. When I say combination, I meant to say combination of the output, not formulas.

The first formula returns multiple columns as we have specified multiple indexes. Whereas the second formula returns multiple row output as it has one column index and multiple search_keys.

This time we have multiple indexes as well as multiple search keys in Vlookup. The output of such a Vloookup formula would be in a matrix form.

=ArrayFormula(vlookup(N2:N4,A1:M7,{3,4,5},0))

Here also the Sum function can sum the total of the matrix. But what about Vlookup with Sum in each row.

I mean to say the total of “Product 3”, “Product 4”, and “Product 5” separately. Here we can use MMULT with Vlookupt.

MMULT Vlookup Matrix Output

See the array reference of the above output on the image. It’s O2:Q4, right? I will use it in an MMULT as matrix 1.

MMULT Syntax:

MMULT(matrix1, matrix2)

Then what about matrix 2?

We want to sum, not multiply the values. So use the below array as matrix 2.

={1;1;1}

So the formula would be as follows.

=mmult(O2:Q4,{1;1;1})

Replace O2:Q4 with the Vlookup formula (refer to the formula on the image above) itself.

=mmult(ArrayFormula(vlookup(N2:N4,A1:M7,{3,4,5},0)),{1;1;1})

We have not yet reached the final formula to sum Vlookup output in each row. We need to automate the matrix 2 in the formula.

Explanation to Matrix 2 Use:

Look at the above formula (only the Vlookup part). We have used 3 index columns. So the formula returns a three-column output.

Since that output is our MMULT matrix 1, we need 3 rows in matrix 2 as per matrix standard. This array {1;1;1} acts as matrix 2. The following formula automates this array (matrix 2).

ArrayFormula(sign(sequence(3,1)))

See the Vlookup after incorporating the above changes.

=mmult(ArrayFormula(vlookup(N2:N4,A1:M7,{3,4,5},0)),ArrayFormula(sign(sequence(3,1))))

Don’t forget to change the number 3 in the Sequence with the number of index columns you use in your Vlookup.

We can further shorten the Vlookup with removing multiple ArrayFormulas.

=ArrayFormula(mmult(vlookup(N2:N4,A1:M7,{3,4,5},0),sign(sequence(3,1))))

The formula that you can see on the image below is slightly different from the one above.

The difference is the use of IFNA around Vlookup to return 0 if the search key is not available in the ‘range’.

This is a must otherwise MMULT may sometimes return #VALUE error.

That’s all about Vlookup with Sum in multiple rows in Google Sheets.

Conclusion

There are plenty of Vlookup related advanced tutorials on this site. Search using the search bar above. Some of the rare Vlookup tutorials are linked below for your quick read.

1. How to Get Dynamic Search Column in Vlookup in Google Sheets.
2. Using Keyword Combinations in Vlookup in Sheets.
3. Vlookup Last Record in Each Group in Google Sheets.
4. Vlookup from Bottom to Top in Google Docs Sheets.
5. Create Hyperlink to Vlookup Output Cell in Google Sheets.
6. Vlookup Result Plus Next ‘n’ Rows in Google Sheets.
7. How to Highlight Vlookup Result Value in Google Sheets.