Excel index match multiple row results

excel excel-formula excel-match
user3224346 picture user3224346 · Jan 22, 2014 · Viewed 13.9k times · Source

I'm stuck on an Excel problem and am hoping someone can assist. I read through 10-15 topics that are similar, but I wasn't able to get anything to work. Here is where I'm at...

I have a large data set containing columns for Year, Name, Total 1, Total 2 (and 20+ other columns). The same names appear in multiple rows based on the yearly totals. On a separate sheet, I have another data set containing Name and would like to pull the data from sheet one into columns as shown below.

enter image description here

I have done this in the past using only one year as the initial data set with the following formula: 

=INDEX(DATARANGE,MATCH([@Name],DATARANGE[Name],0),MATCH("Total 1",DATARANGE[#Headers],0))

The problem I am having is the result of adding multiple years of data to my 1st data set. Is there a way to match the row based on name and year and then return the results of the appropriate column?

Answer

Eilthalearin Kheru picture Eilthalearin Kheru · Aug 15, 2014 
=SUM(($A$2:$A$9=B$16)*($B$2:$B$9=$A17)*($C$2:$C$9))

Enter above in cell B14 as an array formula or below as standard

=SUMPRODUCT(($A$2:$A$9=B$16)*($B$2:$B$9=$A17)*($C$2:$C$9))

You can do the same for total 2 just replace Cs with Ds

And then drag right and down.

Related questions

If two cells match, return value from third

Here's a simple explanation of what I'm having trouble with. Column A: List of 2300 order numbers Column B: Email Address associated with an order number Column C: List of 100 specific order numbers that I need the email address for So, …

Read More
Check if an excel cell exists on another worksheet in a column - and return the contents of a different column

What I want to do is to say if the contents of cell D3 (on current worksheet) exist in column A in the first worksheet (in my case entitled list). (and they always do exist somewhere). Return the contents of …

Read More
Return Max Value of range that is determined by an Index & Match lookup

I need a cell to display the max value of a range who's row is defined by an index and match formula. I know this will be an array function but I'm struggling to get the syntax right. Here is …

Read More