3

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?

Ram
  • 3,092
  • 10
  • 40
  • 56
user3224346
  • 33
  • 1
  • 1
  • 3

3 Answers3

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.

Eilthalearin Kheru
  • 19
  • 4
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)

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • 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. – user3224346 Jan 22 '14 at 17:11
  • 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 – barry houdini Jan 22 '14 at 17:42
  • Still having trouble with this. By making the changes noted, I no longer receive an error, but am getting #n/a results. – user3224346 Jan 22 '14 at 18:49
  • 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` – barry houdini Jan 22 '14 at 19:00
  • 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. – user3224346 Jan 22 '14 at 20:45
  • 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)` – barry houdini Jan 22 '14 at 20:53
  • 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 Jan 22 '14 at 21:24
  • 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! – user3224346 Jan 22 '14 at 21:32
0

If the presented data to be indexed is a table then

This

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

should be corrected to a proper structured reference of

@[Name]

Also since this is an array formula it may not work with structured references at all. You'd be better served with regular cell references. Also if it is not a table only cell references will work.

Sahil Mittal
  • 20,697
  • 12
  • 65
  • 90
Bprime
  • 1
  • 1