Return names ranked based on two criteria

user_16690339 0
Viewed 24

I am trying to return the name of the top N scorers given their

  1. team played for and
  2. position played. I've used INDEX(MATCH(LARGE) to return the top N scorer but haven't been able to successfully add IFS to the LARGE to create my criteria.This is my master list.

I want to find the highest value in colE given colD=KINGMANE and colC=D

In H21 I want to return the D from KINGMANE with the highest FPts. J19 is the team name,, colF is the position I want to look at, and colG is the rank I want to return.

excel
1 Answers

The Syntax you're looking for the multi-conditional lookup based on rank would be the following. I'm assuming your range ends are 40 so adjust accordingly.:

=INDEX([YourTable],MATCH(1,(LARGE($E$1:$E$40,1)=$E$1:$E$40)*($D$1:$D$40="KINGMANE")*($C$1:$C$40=[Position]),0),1)

Breaking this down into references just plug in

=INDEX([YourTable],MATCH(1,(LARGE($E$1:$E$40,[Rank #])=$E$1:$E$40)*($D$1:$D$40="KINGMANE")*($C$1:$C$40"[Position]),0),1)

user_118200460
Related