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.