# Lookup and sum column in Excel

This tutorial shows how to Lookup and sum column in Excel using the example below;

## Formula

=SUM(INDEX(data,0,MATCH(val,header,0)))

## Explanation

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:

=SUM(INDEX(C5:F11,0,MATCH(I6,C4:F4,0)))

### How this formula works

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(I6,C4:F4,0)

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:

=SUM({6;12;4;10;0;9;6})

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