3 Ways To Sum A Vlookup In Excel

When working with data in Excel, it's common to encounter situations where you need to look up values in a table and then sum the corresponding values. This is where the VLOOKUP function comes in handy. However, the VLOOKUP function on its own only returns a single value. What if you need to sum multiple values based on a lookup condition? In this article, we'll explore three ways to sum a VLOOKUP in Excel.

Understanding VLOOKUP

Before diving into the methods for summing a VLOOKUP, let's quickly review how the VLOOKUP function works. The VLOOKUP function is used to search for a value in a table and return a corresponding value from another column. The syntax for the VLOOKUP function is:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value is the value you want to search for.
• table_array is the range of cells that contains the data you want to search.
• col_index_num is the column number that contains the value you want to return.
• [range_lookup] is an optional argument that specifies whether you want an exact match or an approximate match.

Method 1: Using SUMIF and VLOOKUP

One way to sum a VLOOKUP is to use the SUMIF function in combination with the VLOOKUP function. The SUMIF function sums the values in a range based on a condition, while the VLOOKUP function returns the values to be summed.

The syntax for the SUMIF function is:

SUMIF(range, criteria, [sum_range])

• range is the range of cells that you want to sum.
• criteria is the condition that you want to apply to the range.
• [sum_range] is the range of cells that you want to sum.

To sum a VLOOKUP using SUMIF, you can use the following formula:

=SUMIF(VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]), criteria, [sum_range])

This formula looks up the value in the table, returns the corresponding value, and then sums the values in the range that meet the condition.

Example

Suppose you have a table with sales data and you want to sum the sales for a specific region. The table has the following columns:

Region	Sales
North	100
South	200
East	300
West	400

You can use the following formula to sum the sales for the North region:

=SUMIF(VLOOKUP("North", A2:B5, 2, FALSE), ">0", B2:B5)

This formula looks up the value "North" in the table, returns the corresponding sales value, and then sums the sales values that are greater than 0.

Method 2: Using INDEX and MATCH

Another way to sum a VLOOKUP is to use the INDEX and MATCH functions. The INDEX function returns a value at a specified position in a range, while the MATCH function returns the relative position of a value in a range.

The syntax for the INDEX function is:

INDEX(array, row_num, [column_num])

• array is the range of cells that you want to return a value from.
• row_num is the row number that you want to return a value from.
• [column_num] is the column number that you want to return a value from.

The syntax for the MATCH function is:

MATCH(lookup_value, lookup_array, [match_type])

• lookup_value is the value you want to search for.
• lookup_array is the range of cells that you want to search.
• [match_type] is an optional argument that specifies the type of match you want to perform.

To sum a VLOOKUP using INDEX and MATCH, you can use the following formula:

=SUM(INDEX(array, MATCH(lookup_value, lookup_array, [match_type]), [column_num]))

This formula looks up the value in the array, returns the relative position of the value, and then sums the values in the range that correspond to the position.

Example

Suppose you have a table with sales data and you want to sum the sales for a specific region. The table has the following columns:

Region	Sales
North	100
South	200
East	300
West	400

You can use the following formula to sum the sales for the North region:

=SUM(INDEX(B2:B5, MATCH("North", A2:A5, 0)))

This formula looks up the value "North" in the array, returns the relative position of the value, and then sums the sales values that correspond to the position.

Method 3: Using Power Query

A third way to sum a VLOOKUP is to use Power Query. Power Query is a data manipulation tool in Excel that allows you to transform and analyze data.

To sum a VLOOKUP using Power Query, you can follow these steps:

1. Go to the "Data" tab in the ribbon and click on "From Table/Range".
2. Select the table that contains the data you want to sum.
3. Click on "OK" to create a new query.
4. In the query editor, click on "Add Column" and select "Custom Column".
5. In the custom column formula, enter the following formula:

=SUM(VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]))

This formula looks up the value in the table, returns the corresponding value, and then sums the values.

6. Click on "OK" to create the new column.
7. Click on "Close & Load" to load the query into the workbook.

Example

Suppose you have a table with sales data and you want to sum the sales for a specific region. The table has the following columns:

Region	Sales
North	100
South	200
East	300
West	400

You can use the following formula to sum the sales for the North region:

=SUM(VLOOKUP("North", A2:B5, 2, FALSE))

This formula looks up the value "North" in the table, returns the corresponding sales value, and then sums the sales values.

Image Gallery

We hope this article has helped you learn three ways to sum a VLOOKUP in Excel. Whether you use SUMIF and VLOOKUP, INDEX and MATCH, or Power Query, you can now easily sum values based on a lookup condition. Do you have any questions or comments about this article? Please leave them in the section below.