Using Rank Function but with filtering through another criteria as well.

[DCL611]

Hello to anyone who can help and thank you in advance,
I would like to populate a table that is based on the Rank of 1 criteria, "Potential Return", but then also specifically for another criteria which it must match,  "GIC Industry". This criteria includes a list of  over 60 "GIC Industry" labels. Examples being "Software", "Chemicals", or "IT Services". My issue is that I do not know how to create the correct formula so that Excel skips the top ranked name after having already returned it once and move on to the 2nd, 3rd, or 4th-depending on how many times that "GIC Industry" appears and then populate correctly.  I have attached a sheet which has the raw data attached, I have multiple tabs so for purposes of this specific issue I thought I would just attached the view I am trying to adjust and the source from where I am pulling it from. In column B the "Symbol", which is a stock symbol, will populate based on the the "GIC Industry"-Column C- and then the top ranked "Return Potential" for all symbols in that "GIC Industry".  As you can see in "Column J" I have all symbols from a master list populated and Ranked based entirely on their "Return Potential".  My issue is that after the top ranked return potential for "Software"-cellC4- populates into cellB4-"BOX US Equity"-how do I create the logic so that if "Software" appears again below-which it does 5 more times in cells C5, C6, C10, C14, C15- Excel returns the 2nd, 3rd, 4th, 5th, and 6th ranked names in the "Software" Industry name-Column I- into the corresponding cells in Column B? As is shown, the logic I used kept returning the top ranked return potential which was BOX US Equity. If you scroll down to cells H48 and I48 you can see that "NET US Equity" is the 2nd ranked "Software" name by "Return Potential".  I would like to know how to create the logic that would make "NET US Equity" populate into cell B8 because it would be the 2nd top 'Return potential" symbol in the specific Industry of Software.  Thank you in advance and I really hope I explained this somewhat coherently.
 

[#2]

Forgot to add sheet....

[#3]

@DCL611 I used the following formula to get what you want but I'm sure there are others that might work also.  This does assume you have array formulas in your version of excel:

=INDEX(FILTER(H:H,I:I=C4),COUNTIF(C$1:C4,C4))

I did a similar formula for column E (Return Potential) because I assume you will need that too.  I don't know where column D (Size) comes from.  I have attached the sheet for you to review.  I hope that helps.

[#4]

@mtarler  Thank you so very much! Just what I was looking for!

[#5]

Good Morning Mtarler, 

I am having an issue implanting the formula. So without trying to describe my issue in a wordy way I will just provide the sheet i was working on so you can see that on the Portfolio Tab in Column AQ(GIC Industry) that input is coming from column AF and Column AR(Size) is coming from Column AG(Size). What I am attempting to do is simply replace the existing Symbol in the portfolio if there is another symbol in that specific "GIC Industry" that has a better "Return Potential" Column AS. The source for this list is on the "Ranks" tab. Columns X, Y, Z, and AA on that tab are populated with the entire master list of names and sorted by Return Potential. What I am trying to do is ask whether the best possible choice of a particular industry is being chosen purely on a Potential Return basis? I hope this clarifies what I am trying to accomplish, and apologize for the simplification of the original scenario layout.  @DCL611 

[#6]

@DCL611  The formula appears to work (when modified for those column locations:

=IFERROR(INDEX(FILTER(Ranks!Y:Y,Ranks!Z:Z=AQ8),COUNTIF(AQ$8:AQ8,AQ8))," ")

note you might want to use LARGE() instead of INDEX() in the above formula just in case the list isn't already sorted.

[#7]

Unfortunately I am not familiar with the “Filter” function. When I type it into my formula window populates with the following:

“FILTERXML Returns specific data from the XML content using the specific XPath”

Am I doing something in correctly?

My version of Excel is the Office Home & Business 2019, is it possible my version is not adequate or am I simply missing something when trying to apply the formula?

When I pull the version up that was adjusted this is what appears in the Function window:

=IFERROR(INDEX(_xlfn._xlws.FILTER(Ranks!Y:Y,Ranks!Z:Z=AQ8),COUNTIF(AQ$8:AQ8,AQ8))," ")

Once again I truly apologize for any neophyte questions or actions but I simply am not having luck applying the formula when using “Filter” without having the  “ _xlfn._xlws.” in there preceding “Filter”.

Thank you

Best

  David

@mtarler 

[#8 · Accepted solution]

@DCL611  You are not doing anything wrong, your version of Excel doesn't have dynamic arrays (which means you also don't have some of the new functions that come with it like FILTER(), UNIQUE() and some others).  So going back to old school tricks I think this should work for you:

=IFERROR(OFFSET(Ranks!$Y$1,AGGREGATE(15,7, ROW(Ranks!$Z:$Z)/(Ranks!$Z:$Z=$AQ8),COUNTIF($AQ$1:$AQ8,$AQ8))-1,0)," ")

see attached.  If it works for you I hope you like and mark this as best answer 🙂

[#9]

I cannot thank you enough!@mtarler 

[#10]

@mtarler 

@TBCOL 

I found a VERY Simple formula to address this:
https://exceljet.net/formulas/rank-if-formula

=COUNTIFS($V8:$V1958 <<Criteria Range (Obviously infinite, since this is COUNTIFS)>>,V9<<Criteria1>>,$AJ$8:$AJ$1958 <<Values Range>>,"<="&AJ9<<Current Row>>)