INDEX MATCH WITH A SECOND MATCH?

[Kenja824]

My original index match formula was this...
=IF(ISNA(INDEX(E:E,MATCH($T3,$B:$B,0))),0,INDEX(E:E,MATCH($T3,$B:$B,0)))
This searched column B:B for a name of an NFL player"T3" and returned the total Touchdowns for the season which was in column B:B.

I have now placed a total of three years worth of stats in the range. Which means the player's name will be listed in three different rows. In column A:A is the year for each row of stats.

So now I need the formula to return the Touchdowns, which is in column E:E that not only coincides with the correct players name in column B:B, but also the correct year in column A:A

In case I didnt make sense, another way to say it is.... S3 = 2015 .... T3 = Aaron Rodgers
If Aaron Rodgers is in the same row as 2016 OR 2014 it will be ignored.
The number in column E:E and in the same row as Aaron Rodger and 2015 will be returned.

I would upload my workbook but I had a bunch of other formulas all over it in testing and old ones I started with the thing is just one big mess right now. lol I started using VLookups and was switching to Index Match formulas when I ran into this.

 

[SkipVought]

Hi,

Plz either post an actual example (in rows & columns using TGML tags) like...
[pre]
Heading1 Heading2
Cell21 Cell22
Cell31 Cell32
[/pre]
...or upload a sample workbook.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Here's my GUESS and a stab at a solution...
[pre]
Year Player Touchdowns FirstDowns
2014 Rogers 22 333
2015 Rogers 33 444
2016 Rogers 44 555
2014 Smith 55 666
2015 Smith 66 777
2016 Smith 77 888
...
[/pre]

Then the "lookup" which is not a lookup but a SUM with multiple conditions. This example uses Named Ranges for the above table and this summary table starts in cell G1...
[pre]
(G1) Touchdowns FirstDowns
2015 Rogers =SUMPRODUCT((Year=$G2)*(Player=$H2)*INDIRECT(I$1))
[/pre]
...returns 33, the formula in cell I2 and copied to J2. Can be copied DOWN as well.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Guest262653]

I'd just create a Pivot Table with the original data and then make an inquiry into it using GETPIVOTDATA. An example is attached using SkipVought's data.

 

[IDS]

I have updated avscorreia's spreadsheet by adding a composite column (C5&B5), which can then be used with Match to return the row index for a specified player and year, which can be used in the Index function, so you don't need to set up a pivot table. =INDEX(D$5$10,MATCH($P$5&$P$4,$M$5:$M$10,0))

Not that there is anything wrong with using a pivot table.

But there is nothing wrong with using a composite column with Index and Match either.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[SkipVought]

Here's my workbook FYI.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Kenja824]

Wow. Thanks everyone. Lots of good info here. Gonna learn a lot from this too. Now I just need time where bosses leave me alone long enough to try all this out. lol

I have never used Pivot Tables before, so I am going to have to do this both ways so I learn both, the formulas and how to use Pivot Tables. )

I do appreciate this.

 

[IDS]

I should also have said:
Not that there is anything wrong with using Sumproduct either

Each approach has its advantages, and it really comes down to what you are most comfortable (or familiar) with.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[SkipVought]

Here's a solution using TWO MATCH() functions as you originally requested...
[tt]
=INDEX(INDIRECT(I$1),MATCH($G2,OFFSET(Player,MATCH($H2,Player,0)-1,-1,COUNTIF(Player,$H2),1),0),1)
[/tt]

This solution would be necessary when the return value is NUMERIC and can be achieved using some aggregation.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}