Table of Contents
XLOOKUP is a powerful Excel function that allows you to look up a value in a range of cells and return the sum of all matches. To use XLOOKUP to sum all matches, you need to provide the lookup_value, table_array, column_index_num, and range_lookup as arguments. If range_lookup is set to TRUE or omitted, XLOOKUP will search the table_array from top to bottom and return the sum of all matches. If range_lookup is set to FALSE, XLOOKUP will only return the sum of the first match found. You can also use wildcards in the lookup_value to return the sum of multiple matches.
Often you may want to use the XLOOKUP function to look up some value in a range and then sum the corresponding values in another column for all matches.
Unfortunately, the XLOOKUP function in is only capable of returning the value for the first match.
However, you can use the SUM and FILTER functions instead to look up some value in a range and sum the corresponding values in another range for all matches:
=SUM(FILTER(C2:C11, E2=A2:A11))
This particular formula looks in the range C2:C11 and returns the corresponding values in the range A2:A11 for all rows where the value in C2:C11 is equal to E2.
The following example shows how to use this syntax in practice.
Example: Use XLOOKUP to Sum All Matches in Excel
Suppose we have the following dataset in Excel that shows the points scored by basketball players on various teams:
Suppose we would like to look up the value in “Mavs” in the Team column and sum the values in the Points column for all matches.
We can use the following syntax to do so:
=SUM(FILTER(C2:C11, E2=A2:A11))
The following screenshot shows how to use this formula in practice:
We can see that the sum of all values in the Points column for each occurrence of “Mavs” in the Team column is 71.
We can verify this is correct by manually calculating the sum of all values in the Points column for each row where the Team column is “Mavs”:
Sum of Mavs Points: 20 + 39 + 12 = 71