To lookup and return the sum of a column, you can use the a formula based on the INDEX, MATCH and SUM functions. In the example shown, the formula in I7 is:


Generic formula



The core of this formula uses the INDEX and MATCH function in a special way to return a full column instead of a single value. Working from the inside out, the MATCH function is used to find the correct column number for the fruit in I6:


MATCH return 2 inside the INDEX function as the column_num argument, where the array is set to the range C5:F11, which includes data for all fruits.

The tricky part of the formula is the row_num argument, which is set to zero. Setting row to zero causes INDEX to return all values in the matching column in an array like this:


The SUM function then returns the sum of all items in the array, 47.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.