# Sum multiple rows or columns with XLOOKUP

To sum multiple rows or columns based on a lookup value, you can use the SUM function with the XLOOKUP function.

Even if we have a definitive XLOOKUP guide, we learn something special daily. Today’s example will be on how to find the matching value in a table and sum multiple rows or columns. The method we demonstrate completely replaces the recent VLOOKUP and HLOOKUP functions.

## How to SUM multiple rows values based on a lookup value

This solution provides a powerful VLOOKUP alternative. Use a vertical lookup to find the matching value and sum multiple columns in the same row.

For the sake of simplicity, we will use named ranges:

• Products = B3:B9
• Data = C3:E9

Configure the XLOOKUP function arguments:

• lookup_value: G3
• lookup_array: “products”
• return_array: “data”

Formula:

``=XLOOKUP(G2, products, data)``

The result is a dynamic array that contains three numeric values.

Because we want to get the result as a single value instead of an array, we use the SUM function to compile the array into one cell.

Formula:

``=SUM(XLOOKUP(G2, products, data))``

## Steps to SUM multiple column values based on a lookup value

The following example is based on a horizontal lookup and replaces the HLOOKUP function. First, create a horizontal lookup formula to find the matching value and sum multiple rows in the same column.

Our goal is to find the sales in 2022; In this case, we want to find the matching value in the header section.

Create a new named range; “year” will refer to range C2:E2.

Formula:

``=XLOOKUP(G2, year, data)``

The formula will search the lookup value in the header and return the entire column values in case of a match. As we stated before, the result is an array containing all values we want to sum. We want to return the sum, so combine the XLOOKUP function with the SUM function.

Formula:

``=SUM(XLOOKUP(G2, year, data))``

The result is a single value. 