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

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.