Forum Discussion
Solved
Index function with Indirect function within
Hello, I am new to excel goin to college for it and Im struggling with a problem. I need to create a formula In cell N5, enter an INDEX function that will retrieve the name of the movie with the high...
Place in N5 and fill across:
=INDEX(Table1,MATCH(MAX(Table1[[Gross]:[Gross]]),Table1[[Gross]:[Gross]],0),MATCH(N4,Table1[#Headers],0))
Just a guess because I can't see the sheet tabs, M5's value "Highest Grossing Movie" looks to be the name of a sheet. The #REF! might suggest you're not accounting for spaces in the sheet name and not referring to a range in that sheet.
Presuming the sheet name is "Highest Grossing Movies", this is the syntax of the INDIRECT referring to A1:A10 on that sheet. You may change the range as needed.
=INDIRECT("'"&$M$5&"'!"&"A1:A10")
The need for 2 single quotes in the INDIRECT is a good reason why it's a best practice to avoid spaces in the sheet names.
- Are you trying to return the highest grossing movie/genre/company based on the filtered selection?
yes i think so. The end result should look like this:
Thank you for your help btw, much appreciated
Place in N5 and fill across:
=INDEX(Table1,MATCH(MAX(Table1[[Gross]:[Gross]]),Table1[[Gross]:[Gross]],0),MATCH(N4,Table1[#Headers],0))