Hello!
I am currently toying with an excel movie data base and would like to create a list of top rated movies by genre. Below is an INDEX formula that works great to search my database and give me the OVERALL top movies I want but I cannot seem to adapt this formula to have it search by a specific genre.
=INDEX(Sheet2!A$2:A$50,MATCH(1,INDEX((Sheet2!O$2:O$50=LARGE(Sheet2!O$2:O$50,ROWS(C1:C$1)))*(COUNTIF(C1:C$1,Sheet2!A$2:A$50)=0),),0))
Breakdown of the formula above:
Column A is on Sheet 2 that contains all of the movies
Column O is on Sheet 2 that contains all of the movie ratings (1-10)
Column C is on Sheet 1 That contains the list of movies I want sorted by Genre (note the next line will read C1:C$2 per attached worksheet)
I would ultimately like the formula above to adhere to the genre selected on Sheet 1 in cell A2.
Please see attached spreadsheet for a sample of the formula above.
Thank you for the help!
Bookmarks