Index/Match function to return all values based on multiple criteria

0

I have an Excel document where I am trying to list all values that match three criteria.

Sheet1:

Year   Player   Pos   Cluster
2018   Joe      C      1
2018   Ken      RW     2
2018   Bill     C      1
2018   Ryan     C      1  
2017   Joe      C      1
2017   Ken      RW     2

I would like to return all player names that match off of year, pos, and cluster given a name.

Here's what Sheet 2 should look like like:

Player    Pos     Cluster
Joe       C       1

Similar Players
Bill
Ryan

Here's the function I wrote, but it only returns the same name:

=INDEX('Base Player Data'!B:B,MATCH(1,('Base Player Data'!AZ:AZ='Individual Player Projections'!K1)*('Base Player Data'!A:A='Individual Player Projections'!B6)*('Base Player Data'!E:E='Individual Player Projections'!E1),0))

Any Help Would Be Greatly Appreciated!

SD_23

Posted 2019-05-16T16:32:22.417

Reputation: 1

That kind of index match is intended to return the 1st match only.. Need to tweak the formula to consider the previous match. || Inquiry.. in which cell does the formula located? – p._phidot_ – 2019-05-17T07:58:21.887

1"match off of year, pos, and cluster ".. was mentioned.. but Joe have 2 matching years in the list... you need the result for both (2017&2018) years or the year input is explicitly defined? – p._phidot_ – 2019-05-17T08:02:10.720

@p.phidot Just 2018, right now I have that defined as one of of the match arguments. – SD_23 – 2019-06-01T00:35:14.840

(since it was not clearly stated..) so your real " match arguments" input is a name and year.. only.. right? and the output is a list of names .. right? – p._phidot_ – 2019-06-01T05:40:13.740

@p.phidot I'm sorry for the lack of clarification. So my real "Match Arguments" are position, year, and cluster number. The index iterates through the list of players, and I'm hoping to return a list of players that match those criteria. – SD_23 – 2019-06-01T16:54:06.847

I had edited the the question to match your description.. in the "Sheet 2 should look like like.." part. If it is not what you meant, please edit on top of it. – p._phidot_ – 2019-06-03T05:26:41.070

@p.phidot Thank you for editing that. That is what I would want to output to look like. – SD_23 – 2019-06-04T16:15:59.680

No answers