×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

INDEX MATCH WITH A SECOND MATCH?

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.

RE: INDEX MATCH WITH A SECOND MATCH?

Hi,

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: INDEX MATCH WITH A SECOND MATCH?

Here's my GUESS and a stab at a solution...

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

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...
(G1)		Touchdowns	FirstDowns
2015	Rogers	=SUMPRODUCT((Year=$G2)*(Player=$H2)*INDIRECT(I$1))
 
...returns 33, the formula in cell I2 and copied to J2. Can be copied DOWN as well.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: INDEX MATCH WITH A SECOND MATCH?

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:D$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/

RE: INDEX MATCH WITH A SECOND MATCH?

(OP)
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. clown

I do appreciate this.

RE: INDEX MATCH WITH A SECOND MATCH?

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/

RE: INDEX MATCH WITH A SECOND MATCH?

Here's a solution using TWO MATCH() functions as you originally requested...

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

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close