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?

Improve this question

3 Answers 3

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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)

Improve this answer

8 Comments

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.
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
Still having trouble with this. By making the changes noted, I no longer receive an error, but am getting #n/a results.
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
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.
|
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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.