Search code examples
excelexcel-formulaexcel-match

Excel index match multiple row results

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?

Solution

  • Change the first MATCH function to something like this:

    =MATCH(1,INDEX(([@Name]=DATARANGE[Name])*([@Year]=DATARANGE[Year]),0),0)

    so as part of your whole formula that would be this

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

    Another way you can use for returning numbers only (as here) is like this: (with cell refs for simplicity).

    =SUMPRODUCT((A2:A9=2013)*(B2:B9="name x")*(C1:D1="Total 1"),C2:D9)