INDEX MATCH WITH A SECOND MATCH?
INDEX MATCH WITH A SECOND MATCH?
(OP)
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.
=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.





RE: INDEX MATCH WITH A SECOND MATCH?
Plz either post an actual example (in rows & columns using TGML tags) like...
...or upload a sample workbook.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: INDEX MATCH WITH A SECOND MATCH?
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...
...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!
RE: INDEX MATCH WITH A SECOND MATCH?
RE: INDEX MATCH WITH A SECOND MATCH?
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/
RE: INDEX MATCH WITH A SECOND MATCH?
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: INDEX MATCH WITH A SECOND MATCH?
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.
RE: INDEX MATCH WITH A SECOND MATCH?
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/
RE: INDEX MATCH WITH A SECOND MATCH?
=INDEX(INDIRECT(I$1),MATCH($G2,OFFSET(Player,MATCH($H2,Player,0)-1,-1,COUNTIF(Player,$H2),1),0),1)
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!