Lookup & SUM values with INDEX and MATCH function in Excel

In this article, we will learn how to Lookup & SUM values with INDEX and MATCH function in Excel.
For the formula to understand first we need to revise a little about the three functions

1. SUM
2. INDEX
3. MATCH

SUM function adds all the numbers in a range of cells and returns the sum of these values.
INDEX function returns the value at a given index in an array.

MATCH function returns the index of the first appearance of the value in an array ( single dimension array ).

Now we will make a formula using the above functions. Match function will return the index of the lookup value in the header field. The index number will now be fed to the INDEX function to get the values under the lookup value.
Then the SUM function will return the sum from the found values.

Use the Formula:

The above statements can be complicated to understand. So let’s understand this by using the formula in an example

Here we have a list of marks of students and we need to find the total marks for a specific person (Gary) as shown in the snapshot below.

Use the formula in the C2 cell:

Explanation:

• The MATCH function matches the first value with the header array and returns its position 3 as a number.
• The INDEX function takes the number as the column index for the data and returns the array { 92 ; 67 ; 34 ; 36 ; 51 } to the argument of the SUM function
• The SUM function takes the array and returns the SUM in the cell.

Here values to the function is given as cell reference.

As you can see in the above snapshot, we got the SUM of the marks of student Gary. And it proves the formula works fine and for doubts see the below notes for understanding.

Notes:

1. The function returns the #NA error if the lookup array argument to the MATCH function is 2 - D array which is the header field of the data..
2. The function matches exact value as the match type argument to the MATCH function is 0.
3. The lookup value can be given as cell reference or directly using quote symbol (").
4. The SUM function ignores the text values in the array received from the INDEX function.

Hope you understood how to use the LOOKUP function in Excel. Explore more articles on Excel lookup value here. Please feel free to state your queries below in the comment box. We will certainly help you.

Related Articles

Use INDEX and MATCH to Lookup Value

SUM range with INDEX in Excel

How to use the SUM function in Excel

How to use the INDEX function in Excel

How to use the MATCH function in Excel

How to use LOOKUP function in Excel

How to use the VLOOKUP function in Excel

How to use the HLOOKUP function in Excel

Popular Articles

Edit a dropdown list

If with conditional formatting

If with wildcards

Vlookup by date