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.