Switch Function - Two Criteria or Expressions

Hi All,

I recently discovered the Switch Function and it is remarkable. I am able to get what I need based on one expression, but I haven't been able to wrap my head around basing off of two sets of criteria or expressions.

The attached is College Football and I want to find the Top 5 impressions per week. Cell B334 is where I am writing my formula, referencing Cell B333 as my initial criteria/expression, where I can toggle between the weeks using the scroll bar. In addition to seeing what the Top 5 impressions per week are, I'd like to be able to see the Top N impressions per week by Conference, referencing Cell C328. For example which Big Ten game had the highest impressions in Week 10. Right now, I only know which Week 10 game had the highest impressions among all of the games. I searched around and saw some posts about starting with an IF statement or doing a SWITCH TRUE, but I am punting it here to see if there is a better way because I couldn't get those to work.

Thank you!

BTray90

Thanks for taking a look at this, Bernie. I wasn't able to get it to work. I cleared out the Rows/Columns from my previous work and am getting a circular reference, for both formulas. I don't know what is overlapping. Also, what does the -1 represent?

Hi John, Thanks for taking a look at this. I was able to get both formulas to work! Really cool how much more simple that is. However, I am not clear on how to apply the 2nd condition/expression where I would like to see a similar Rank of Impressions by a particular week (Cell B333) AND by Conference (Cell C328). For example, what are the top 5 impressions in Week 9 by BIG TEN

That works and I was able to figure out a way to get the ranker underneath the table by fixing the range so there isn't a circular reference. Your formula worked for the Week * Conference. Thank You!
Bonus Point, anyway to fix the formula for the user to be able to look at a total Top 5, irrespective of the conference, and then look at a Top 5 with the Conference filter in a separate click. I'd like for it to be housed in the same table. I know I could build two rankers, but think it would be cleaner to have one table of rankers that can do those two functions. For example, if the Conference cell is blank in C328, could that default to being No Conference and results would be Total Top 5? Probably a stretch, but wondering if there is another component to switch off one if blank (or some other function) while also being able to apply if there is text in Cell C328 when requested.

So simple, so amazing! Thanks Bernie! This will have widespread applications. I will credit you with the reputation and close out the thread.