Excel index match multiple row results

Question

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.

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 1

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)

Solution 2

=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.

Comments

[user3224346]

Thanks for the quick response Barry. There seems to be a problem with that formula. specifically with the [@Year] after the *. Excel will not take the formula.

[barry houdini]

OK, I was mimicking your formula but that's not correct here - replace [@Year] and [@Name] in my suggestion with cell refs containing year and name in summary table

[user3224346]

Still having trouble with this. By making the changes noted, I no longer receive an error, but am getting #n/a results.

[barry houdini]

OK, try testing the new MATCH part separately - what do you get with =MATCH(1,INDEX(("name x"=DATARANGE[Name])*(2013=DATARANGE[Year]),0),0) or the same but with cell references in place of "name x" and 2013

[user3224346]

thank you for your continued help Barry. it's much appreciated. Here is the exact formula that I have entered: =MATCH(1,INDEX((B2=data1[Name])*($D$1=data1[Year]),0),0) and the results are #N/A.

[barry houdini]

OK, I assume D1 contains just the year like 2013 not a formatted date? It's possible that the year is a number in D1 and a text value in your table (or vice versa) and that might mean you don't get a match, what does this give you - =MATCH(1,INDEX((B2=data1[Name])*($D$1&""=data1[Year]),0),0)

[user3224346]

This is getting weird. lol. I did check to make sure the D1 value is formatted the same was as the datatable and it is, however, if I change the reference to some unused cell and enter the same number (2012), the previous code that you had me check DOES work. So there is some problem with my Headers. The newest forumla did not work, but this does as long as I reference a cell not in my headers: =MATCH(1,INDEX((B2=data1[Name])*($D$1=data1[Year]),0),0)

[user3224346]

Thank you again for your help. I was able to get the entire formula working by changing my year reference away from the Header of my second sheet. Not exactly what I was looking for but it will work. Thanks again!