Power Spreadsheets

Excel and VBA tutorials and training. Learn how to use Microsoft Excel and Visual Basic for Applications now.

Excel VLookup Sum Multiple Column Values (with XLOOKUP) in 4 Easy Steps (+ Free Easy-To-Adjust Excel Workbook Example)

Excel VLookup Sum Multiple Column Values (with XLOOKUP) in 4 Easy Steps (+ Free Easy-To-Adjust Excel Workbook Example)In this Excel VLookup Sum Multiple Column Values (with XLOOKUP) Tutorial, you learn how to:

  • Carry out a VLookup; and
  • Sum multiple column values in the applicable row;

With the XLOOKUP function.

The VLookup sum multiple column values (with XLOOKUP) formula template/structure you learn in this Tutorial isn't the only way to:

  • Carry out a VLookup; and
  • Sum multiple column values.

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

  • Excel VLOOKUP Sum Multiple Columns (Values).
  • 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 Column Values (with XLOOKUP) 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.


Get immediate free access to the Excel VLookup Sum Multiple Column Values (with XLOOKUP) example workbook

This Excel VLookup Sum Multiple Column Values (with XLOOKUP) Tutorial is part of a more comprehensive series of Excel VLookup Tutorials.

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:

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:

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


Get immediate free access to the Excel VLookup Sum Multiple Column Values (with XLOOKUP) example workbook

The VLookup Sum Multiple Column Values (with XLOOKUP) Formula Template/Structure

The following is the VLookup sum multiple column values (with XLOOKUP) formula template/structure I explain (step-by-step) in the Sections below.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-xlookup-sum-columns/
=SUM(XLOOKUP(LookupValue,ColumnWhereYouSearchIn,ColumnsWithValuesToAdd))


Get immediate free access to the Excel VLookup Sum Multiple Column Values (with XLOOKUP) example workbook

The Example Before VLookup Sum Multiple Column Values (with XLOOKUP)

This Excel VLookup Sum Multiple Column Values (with XLOOKUP) 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.


Get immediate free access to the Excel VLookup Sum Multiple Column Values (with XLOOKUP) example workbook

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

(1) Table 1 (Cells A6 to D26).

The table:

  • With the data.
  • Where you search with the VLookup sum multiple column values (with XLOOKUP) example formula.

Main characteristics:

  • 4 columns (Salesperson, Units 1, Units 2, Units 3).
  • 1 header row (row 6).
  • 20 entries (rows 7 to 26).

(2) Table 2 (Cells F6 to H7).

The table where you:

  • Set up the VLookup sum multiple column values (with XLOOKUP) example formula.
  • Display the results.

This table has the following 3 columns:

  • Salesperson: Cell F7 contains the lookup value.
  • Total Units: Cell G7:
    • Is currently empty.
    • Will store the VLookup sum multiple column values (with XLOOKUP) example formula.
  • Formula: Cell H7:
    • Currently displays the #N/A error.
    • Will display the VLookup sum multiple column values (with XLOOKUP) example formula I enter in cell G7.
VLookup sum multiple columns with XLOOKUP worksheet before formula


Get immediate free access to the Excel VLookup Sum Multiple Column Values (with XLOOKUP) example workbook

Step 1: Specify the Lookup Value

Specify the first argument of the XLOOKUP function: lookup_value.

lookup_value is the value you search for in the column you search in (and specify in step #2).

Consider the following when setting up the XLOOKUP function for the VLookup sum multiple column values (with XLOOKUP) formula:

  • The XLOOKUP function is available in Excel 2021 and later (including Excel 365).
  • By default, XLOOKUP:
    • Searches for an exact match of the lookup_value.
    • Works with the first entry matching the lookup_value.
    • Returns the #N/A error if no exact match of the lookup_value is found.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-xlookup-sum-columns/
XLOOKUP(LookupValue,


Get immediate free access to the Excel VLookup Sum Multiple Column Values (with XLOOKUP) example workbook

Step 1 Example

The lookup value for the VLookup sum multiple column values (with XLOOKUP) example formula is stored in cell F7.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-xlookup-sum-columns/
XLOOKUP(F7
Lookup value to sum multiple column values with XLOOKUP


Get immediate free access to the Excel VLookup Sum Multiple Column Values (with XLOOKUP) example workbook

Step 2: Identify the Column You Search In

Specify the second argument of the XLOOKUP function: lookup_array.

The lookup_array argument is the cell range where you search in (the column with the applicable data) for the lookup value (you specified in step #1).

As a general rule: When creating a VLookup sum multiple column values (with XLOOKUP) formula, specify the lookup_array as a single-column (1 column, several rows) cell range.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-xlookup-sum-columns/
XLOOKUP(LookupValue,ColumnWhereYouSearchIn,


Get immediate free access to the Excel VLookup Sum Multiple Column Values (with XLOOKUP) example workbook

Step 2 Example

The column the VLookup sum multiple column values (with XLOOKUP) example formula looks in is cells A7 to A26 ($A$7:$A$26, when using absolute references).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-xlookup-sum-columns/
XLOOKUP(F7,$A$7:$A$26,
Table you search in for VLookup sum multiple column values with XLOOKUP


Get immediate free access to the Excel VLookup Sum Multiple Column Values (with XLOOKUP) example workbook

Step 3: Identify the Multiple Columns with the Values to Sum (Add)

Specify the third argument of the XLOOKUP function: return_array.

When creating a VLookup sum multiple column values (with XLOOKUP) formula, the return_array argument is the multiple-column cell range with the values to sum (add).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-xlookup-sum-columns/
XLOOKUP(LookupValue,ColumnWhereYouSearchIn,ColumnsWithValuesToAdd)


Get immediate free access to the Excel VLookup Sum Multiple Column Values (with XLOOKUP) example workbook

Step 3 Example

The multiple columns with the values to sum (add) (by the VLookup sum multiple column values (with XLOOKUP) example formula) are in cells B7 to D26 ($B$7:$D$26, when using absolute references).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-xlookup-sum-columns/
XLOOKUP(F7,$A$7:$A$26,$B$7:$D$26)

The image below illustrates how this expression does the following:

  • Carry out a VLookup; and
  • Return values from the applicable columns.

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

In the image below:

  • Row 9 (Units to add; cells G9 to I9) displays the values stored in columns B, C, and D (cells B24, C24, D24). These are the columns with the values to sum (add) by the VLookup sum multiple column values (with XLOOKUP) example formula.
  • Cell G10 displays the formula stored in cell G9.
XLOOKUP function to VLookup sum multiple column values


Get immediate free access to the Excel VLookup Sum Multiple Column Values (with XLOOKUP) example workbook

Step 4: Sum (Add) the Values Returned by the XLOOKUP Function

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

  1. Call the SUM function; and
  2. Set the number1 argument of the SUM function to the XLOOKUP function (you created in steps #1 to #3).
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-xlookup-sum-columns/
=SUM(XLOOKUP(LookupValue,ColumnWhereYouSearchIn,ColumnsWithValuesToAdd))


Get immediate free access to the Excel VLookup Sum Multiple Column Values (with XLOOKUP) example workbook

Step 4 Example

Considering the XLOOKUP function I created in steps #1 to #3.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-xlookup-sum-columns/
=SUM(XLOOKUP(F7,$A$7:$A$26,$B$7:$D$26))
VLookup sum multiple column values with XLOOKUP formula example


Get immediate free access to the Excel VLookup Sum Multiple Column Values (with XLOOKUP) example workbook

Download the VLookup Sum Multiple Column Values (with XLOOKUP) Example Workbook

This Excel VLookup Sum Multiple Column Values (with XLOOKUP) 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.


Get immediate free access to the Excel VLookup Sum Multiple Column Values (with XLOOKUP) example workbook

This Excel VLookup Sum Multiple Column Values (with XLOOKUP) Tutorial is part of a more comprehensive series of Excel VLookup Tutorials.

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:

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:

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

Subscribe
Notify of
guest
1 Comment
Most Voted
Newest Oldest
Inline Feedbacks
View all comments

I publish a lot of Tutorials and Training Resources about Microsoft Excel and VBA. Here are some of my most popular Excel Training Resources:

  1. Free Excel VBA Email Course
  2. Excel Macro Tutorial for Beginners
  3. Excel Power Query (Get and Transform) Tutorial for Beginners
  4. Excel Keyboard Shortcut Cheat Sheet
  5. Excel Resources
Terms and Conditions

Privacy Policy

Limit of Liability and Disclaimer of Warranty

Affiliate Disclosure