Excel VLOOKUP Sum Multiple Columns (Values) in 6 Easy Steps

In this Excel VLOOKUP Sum Multiple Columns (Values) Tutorial, you learn how to:

Carry out a VLOOKUP; and
Sum multiple columns (values) in the applicable row.

The VLOOKUP sum multiple columns (values) formula template/structure you learn in this Tutorial isn't the only way to:

Carry out a VLookup; and
Sum multiple columns (values).

You may (also) be interested in the following alternative formula templates/structures:

Excel VLookup Sum Multiple Column Values (with XLOOKUP).
Excel VLookup Sum Multiple Rows and Columns.

I link to these Tutorials in the Related Excel Training Materials and Resources Section below.

This Excel VLOOKUP Sum Multiple Columns (Values) Tutorial is accompanied by an Excel workbook with the data and formulas I use when describing the step-by-step process below. Get this example workbook (for free) by clicking the button below.

Table of Contents

This Excel VLOOKUP Sum Multiple Columns (Values) Tutorial is part of a more comprehensive series of Excel VLookup Tutorials.

Excel VLOOKUP Tutorial (under development): Click here to open.
Excel VLOOKUP from Another Sheet in 4 Easy Steps: Click here to open.
Excel VLOOKUP Compare 2 Columns and Find Matches in 4 Easy Steps: Click here to open.
Excel VLookup Sum Multiple Row Values (in Same Column) in 4 Easy Steps: Click here to open.
Excel VLOOKUP Multiple Columns in 5 Easy Steps: Click here to open.
Excel VLookup Sum Multiple Column Values (with XLOOKUP) in 4 Easy Steps: Click here to open.
Excel VLookup Sum Multiple Rows and Columns in 3 Easy Steps: Click here to open.
Excel VLookup Multiple Criteria with INDEX MATCH in 4 Easy Steps: Click here to open.
Excel VLookup Multiple Criteria with XLOOKUP in 2 Easy Steps: Click here to open.
Excel VLookup Multiple Criteria with the FILTER Function in 2 Easy Steps: Click here to open.
Excel VLOOKUP Return Multiple Values with Helper Column in 4 Easy Steps: Click here to open.
Excel VLookup Return Multiple Values with the INDEX Function in 7 Easy Steps: Click here to open.
Excel VLookup Return Multiple Values with the FILTER Function in 2 Easy Steps: Click here to open.
Excel VLookup Return Multiple Values in One Cell Separated by a Comma in 4 Easy Steps: Click here to open.
Excel VLOOKUP Multiple Sheets in 10 Easy Steps: Click here to open.
Excel VLOOKUP Multiple Sheets in Different Workbook in 10 Easy Steps: Click here to open.
Excel VLOOKUP Sheet in Multiple Different Workbooks in 10 Easy Steps: Click here to open.

My Excel XLOOKUP Tutorial (click here to open) may help you:

Better understand and implement the contents below.
Better understand the differences between the XLOOKUP and VLOOKUP functions (XLOOKUP vs. VLOOKUP).

You can find more Excel Tutorials in the organized Tutorials Archive: Click here to visit the Archives. The following are some of my most popular Excel Tutorials:

Excel Macro Tutorial for Beginners: Click here to open.
Excel Power Query (Get & Transform) Tutorial for Beginners: Click here to open.
350 + Excel Keyboard Shortcuts And Hotkeys: Click here to open.

If you want to learn more about Excel essentials, Excel formulas, and similar Excel topics, you may be interested in taking one (or more) Excel Courses: Click here to learn more about these Excel Courses (affiliate link). The following are some of the topics covered in these Excel Courses:

Excel essentials and must-know skills.
Advanced Excel Formulas.
Excel Tables.
Pivot Tables.
Dashboards.
Power Pivot.
Power Query.

If you want to start learning how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

Premium Courses at the Power Spreadsheets Academy: Click here to open.
Books at the Power Spreadsheets Library: Click here to open.

If you need consulting services, you may want to consider working with ExcelRescue. ExcelRescue is my usual suggestion for people who (like you) may need help with Excel tasks/projects: Click here to visit ExcelRescue (affiliate link).

The VLOOKUP Sum Multiple Columns (Values) Formula Template/Structure

The following is the VLOOKUP sum multiple columns (values) formula template/structure I explain (step-by-step) in the Sections below.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-sum-multiple-columns/
=SUM(VLOOKUP(LookupValue,CellRangeWhereYouLookIn,NumbersOfColumnsWithValuesToAdd,TrueOrFalse))

This is an array formula. If you're working with Excel 2019 or earlier, enter this formula by pressing “Ctrl + Shift + Enter”. In these cases, Excel wraps the formula in curly braces ({}).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-sum-multiple-columns/
{=SUM(VLOOKUP(LookupValue,CellRangeWhereYouLookIn,NumbersOfColumnsWithValuesToAdd,TrueOrFalse))}

The Example Before VLOOKUP Sum Multiple Columns (Values)

The example worksheet has 3 tables/sections with the following characteristics:

(1) Table 1 (Cells A8 to D28).

The table:

With the data.
Where you search with the VLOOKUP sum multiple columns (values) example formula.

Main characteristics:

4 columns (Salesperson, Units 1, Units 2, Units 3).
1 header row (row 8).
20 entries (rows 9 to 28).

(2) Table 2 (Cells F8 to H9).

The table:

Where you set up the VLOOKUP sum multiple columns (values) example formula.
Display the results.

This table has the following 3 columns:

Salesperson: Cell F9 contains the lookup value.
Total Units: Cell G9:
- Is currently empty.
- Will store the VLOOKUP sum multiple columns (values) example formula.
Formula: Cell H9:
- Currently displays the #N/A error.
- Will display the VLOOKUP sum multiple columns (values) example formula I enter in cell G9.

(3) Table 3 (Cells F11 to F14).

1 column (Column Numbers) with numbers. These numbers match the numbers of the multiple columns (see cells B6 to D6) in Table 1 the VLOOKUP sum multiple columns (values) example formula sums (adds).

VLOOKUP sum multiple columns worksheet before formula

Step 1: Specify the Lookup Value

Specify the first argument of the VLOOKUP function: lookup_value.

lookup_value is the value you search for in the first/leftmost column of the table you work with. The VLOOKUP function is case insensitive.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-sum-multiple-columns/
VLOOKUP(LookupValue,

Step 1 Example

The lookup value for the VLOOKUP sum multiple columns (values) example formula is stored in cell F9.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-sum-multiple-columns/
VLOOKUP(F9,

Lookup value to sum multiple column values

Step 2: Specify the Cell Range You Look In

Specify the second argument of the VLOOKUP function: table_array.

The table_array argument is the cell range where you look in (the table with the applicable data).

The first/leftmost column of the table (you specify as table_array) must contain the lookup_value (you specified in step #1).
As a general rule, if the first/leftmost column in the table contains duplicate values (and you look up one of those duplicate values), the VLOOKUP function works with the first entry matching the lookup value.
The cell range you specify as table_array argument must (also) contain the column with the value to return.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-sum-multiple-columns/
VLOOKUP(LookupValue,CellRangeWhereYouLookIn,

Step 2 Example

The cell range the VLOOKUP sum multiple columns (values) example formula looks in is cells A9 to D28 ($A$9:$D$28, when using absolute references).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-sum-multiple-columns/
VLOOKUP(F9,$A$9:$D$28,

Table for VLOOKUP sum multiple column values

Step 3: Create an Array with the Numbers of the Multiple Columns with the Values to Sum (Add)

Specify the third argument of the VLOOKUP function: col_index_num.

The col_index_num argument is the number of the column (in the table you specified in step #2) with the value to return.

The first column in the table is column 1.
The second column in the table is column 2.
…
The nth column in the table is column n.

When creating a VLOOKUP sum multiple columns (values) formula, you can specify the col_index_num argument in several ways, including the following 3:

Using cell references (to the cells containing the applicable column numbers).
Working with a function, such as SEQUENCE. The SEQUENCE function:
- Is available in Excel 2021 and later (including Excel 365).
- Allows you to generate a list of sequential numbers (in an array).
Hardcoding the multiple column numbers.

If you choose to hardcode the multiple column numbers (with the values to sum), specify the col_index_num argument as follows:

Specify the multiple column numbers (with the values to sum) as a comma-delimited list. In other words: Use commas (,) to separate the multiple column numbers (NumberOfColumnWithValueToAdd1,NumberOfColumnWithValueToAdd2,…,NumberOfColumnWithValueToAdd#).
Wrap the comma-delimited list of multiple column numbers (with the values to sum) in curly braces ({NumberOfColumnWithValueToAdd1,NumberOfColumnWithValueToAdd2,…,NumberOfColumnWithValueToAdd#}).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-sum-multiple-columns/
VLOOKUP(LookupValue,CellRangeWhereYouLookIn,NumbersOfColumnsWithValuesToAdd,

Step 3 Example

The numbers of the multiple columns (in the table I specified in step #2) with the values to sum (add) (by the VLOOKUP sum multiple columns (values) example formula) are stored in cells F12 to F14 ($F$12:$F$14, when using absolute references). These numbers match the column numbers (see cells B6 to D6) of the table I specified in step #2.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-sum-multiple-columns/
VLOOKUP(F9,$A$9:$D$28,$F$12:$F$14,

Column numbers for VLOOKUP sum multiple column values

Step 4: Specify Whether You Want an Approximate or Exact Match

Specify the fourth argument of the VLOOKUP function: range_lookup.

The range_lookup argument specifies whether you want an approximate or exact match, as follows:

TRUE: The VLOOKUP function returns an approximate match. An approximate match lookup is (also) known as a range lookup (you lookup a value inside a range of values).
FALSE: The VLOOKUP function returns an exact match.

If you omit the range_lookup argument, the VLOOKUP function:

Returns an approximate match; but
- (Usually) Returns an exact match if:
- An exact match exists; and
The first/leftmost column in the table (you specified in step #2) is sorted in ascending order.

If you want an approximate match (set the range_lookup argument to TRUE, or omit the range_lookup argument):

The first/leftmost column (in the table you specified in step #2) must be sorted in ascending order.
If the lookup value (you specified in step #1) is:
- Smaller than the first value in the first/leftmost column of the table (you specified in step #2), the VLOOKUP function returns the #N/A error.
- Larger than the last value in the first/leftmost column of the table (you specified in step #2), the VLOOKUP function works with that last value in the column.

If you want an exact match (set the range_lookup argument to FALSE) and the VLOOKUP function fails to find an exact match for the lookup value (you specified in step #1) in the table (you specified in step #2), the VLOOKUP function returns the #N/A error.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-sum-multiple-columns/
VLOOKUP(LookupValue,CellRangeWhereYouLookIn,NumbersOfColumnsWithValuesToAdd,TrueOrFalse)

Step 4 Example

The VLOOKUP sum multiple columns (values) example formula works with an exact match. I set the range_lookup argument to FALSE.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-sum-multiple-columns/
VLOOKUP(F9,$A$9:$D$28,$F$12:$F$14,FALSE)

The image below illustrates how this expression does the following:

Carry out a VLOOKUP; and
Return values from the columns I specified in step #3.

In other words: This expression returns an array with the values to sum (add) by the VLOOKUP sum multiple columns (values) example formula.

In the image below:

Column F (Units to add; cells F9 to F11) displays the values stored in columns B, C, and D (cells B26, C26, D26). These are:
- Columns 2, 3, and 4 in the table I specified in step #2.
- The columns with the values to sum (add) by the VLOOKUP sum multiple columns (values) example formula.
Cell G9 displays the formula stored in cell F9.

VLOOKUP function to sum multiple column values

Step 5: Sum (Add) the Values Returned by the VLOOKUP Function

Do the following to sum (add) the values returned by the VLOOKUP function (you created in steps #1 to #4):

Call the SUM function; and
Set the number1 argument of the SUM function to the VLOOKUP function (you created in steps #1 to #4).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-sum-multiple-columns/
=SUM(VLOOKUP(LookupValue,CellRangeWhereYouLookIn,NumbersOfColumnsWithValuesToAdd,TrueOrFalse))

Step 5 Example

Considering the VLOOKUP function I created in steps #1 to #4.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-sum-multiple-columns/
=SUM(VLOOKUP(F9,$A$9:$D$28,$F$12:$F$14,FALSE))

VLOOKUP sum multiple columns (values) formula example

Step 6: Enter the Formula as an Array Formula

The VLOOKUP sum multiple columns (values) formula template/structure you learned in this Tutorial is an array formula.

If you're working with Excel 2019 or earlier, enter this VLOOKUP sum multiple columns (values) formula by pressing “Ctrl + Shift + Enter”. In these cases, Excel wraps the formula in curly braces ({}).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-sum-multiple-columns/
{=SUM(VLOOKUP(LookupValue,CellRangeWhereYouLookIn,NumbersOfColumnsWithValuesToAdd,TrueOrFalse))}

Step 6 Example

The image below displays the VLOOKUP sum multiple columns (values) example formula wrapped in curly braces ({}) if I press “Ctrl + Shift + Enter” to enter it. The results are the same as those displayed in step #5.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-sum-multiple-columns/
{=SUM(VLOOKUP(F9,$A$9:$D$28,$F$12:$F$14,FALSE))}

VLOOKUP sum multiple columns (values) array formula example

Download the VLOOKUP Sum Multiple Columns (Values) Example Workbook

This Excel VLOOKUP Sum Multiple Columns (Values) Tutorial is accompanied by an Excel workbook with the data and formulas I use when describing the step-by-step process above. Get this example workbook (for free) by clicking the button below.

This Excel VLOOKUP Sum Multiple Columns (Values) Tutorial is part of a more comprehensive series of Excel VLookup Tutorials.

Excel VLOOKUP Tutorial (under development): Click here to open.
Excel VLOOKUP from Another Sheet in 4 Easy Steps: Click here to open.
Excel VLOOKUP Compare 2 Columns and Find Matches in 4 Easy Steps: Click here to open.
Excel VLookup Sum Multiple Row Values (in Same Column) in 4 Easy Steps: Click here to open.
Excel VLOOKUP Multiple Columns in 5 Easy Steps: Click here to open.
Excel VLookup Sum Multiple Column Values (with XLOOKUP) in 4 Easy Steps: Click here to open.
Excel VLookup Sum Multiple Rows and Columns in 3 Easy Steps: Click here to open.
Excel VLookup Multiple Criteria with INDEX MATCH in 4 Easy Steps: Click here to open.
Excel VLookup Multiple Criteria with XLOOKUP in 2 Easy Steps: Click here to open.
Excel VLookup Multiple Criteria with the FILTER Function in 2 Easy Steps: Click here to open.
Excel VLOOKUP Return Multiple Values with Helper Column in 4 Easy Steps: Click here to open.
Excel VLookup Return Multiple Values with the INDEX Function in 7 Easy Steps: Click here to open.
Excel VLookup Return Multiple Values with the FILTER Function in 2 Easy Steps: Click here to open.
Excel VLookup Return Multiple Values in One Cell Separated by a Comma in 4 Easy Steps: Click here to open.
Excel VLOOKUP Multiple Sheets in 10 Easy Steps: Click here to open.
Excel VLOOKUP Multiple Sheets in Different Workbook in 10 Easy Steps: Click here to open.
Excel VLOOKUP Sheet in Multiple Different Workbooks in 10 Easy Steps: Click here to open.

My Excel XLOOKUP Tutorial (click here to open) may help you:

Better understand and implement the contents above.
Better understand the differences between the XLOOKUP and VLOOKUP functions (XLOOKUP vs. VLOOKUP).

You can find more Excel Tutorials in the organized Tutorials Archive: Click here to visit the Archives. The following are some of my most popular Excel Tutorials:

Excel Macro Tutorial for Beginners: Click here to open.
Excel Power Query (Get & Transform) Tutorial for Beginners: Click here to open.
350 + Excel Keyboard Shortcuts And Hotkeys: Click here to open.

Excel essentials and must-know skills.
Advanced Excel Formulas.
Excel Tables.
Pivot Tables.
Dashboards.
Power Pivot.
Power Query.

If you want to start learning how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

Premium Courses at the Power Spreadsheets Academy: Click here to open.
Books at the Power Spreadsheets Library: Click here to open.