Jul 01 2022 10:51 PM
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 highest gross sales. Write the function using the INDIRECT function as the array argument and then copy the formula across to P5. Im not sure how to do this. Any help is greatly appreciated
Jul 02 2022 04:21 AM
=INDEX(INDIRECT($F$2),MATCH(G1,movie,0))
From your screenshot i can't tell what you exactly want to do. However it's possible to combine INDEX and MATCH with INDIRECT and to drag the formula across a range.
Jul 02 2022 10:57 AM - edited Jul 02 2022 11:01 AM
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.
Jul 02 2022 04:04 PM
When I use that formula I get an error, did I not correctly type it in? I need to use the INDIRECT function to return a reference to a text string and my prof said I am not returning a Movie Name by referencing cell M5. The MATCH function should be looking for the maximum value in the list. Screenshot given below is the entire table with no filteres.
Jul 02 2022 04:05 PM
Jul 02 2022 05:01 PM
Jul 02 2022 05:08 PM
yes i think so. The end result should look like this:
Thank you for your help btw, much appreciated
Jul 02 2022 05:42 PM - edited Jul 02 2022 05:51 PM
Solution
Place in N5 and fill across:
=INDEX(Table1,MATCH(MAX(Table1[[Gross]:[Gross]]),Table1[[Gross]:[Gross]],0),MATCH(N4,Table1[#Headers],0))
Jul 02 2022 06:12 PM
Jul 02 2022 06:29 PM
Step through the INDEX arguments:
array: Where will your return come from? We give it Table1 because your return might come from one of 3 columns depending on the MATCH results.
Row_num: MATCH(MAX(Table1[[Gross]:[Gross]]),Table1[[Gross]:[Gross]],0)
The MAX obtains the maximum value from the Gross column of the table. Reference to the Gross column appears as Table1[[Gross]:[Gross]] so the column names do not change when you drag the fill handle right.
MATCH obtains the position of the MAX result within the Gross column. That's your row number.
Col_num: MATCH(N4,Table1[#Headers],0)
Here MATCH is obtaining the Column number where the results will come from.
Essentially, with this formula you're obtaining the Row and Column intersection of the table.
Jul 02 2022 05:42 PM - edited Jul 02 2022 05:51 PM
Solution
Place in N5 and fill across:
=INDEX(Table1,MATCH(MAX(Table1[[Gross]:[Gross]]),Table1[[Gross]:[Gross]],0),MATCH(N4,Table1[#Headers],0))