Question

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.

015 D E F H 1 J K L M N O P Q 1 2 Company Genre Movie Name Highest Grossing Movie Year 1986 1988 1990 1993 1994 1998 1999 2000 2001 2002 2004 2010 PG-13 2011 2012 2013 2014 3 4 5 Country Genre Gross StarActor Year - Movie MovieName Movie Rating - 6 USA Animation $ 149,260,504.00 Antonio Banderas 2011 Mov-5962 Puss in Boots PG 7 USA Comedy $ 34,099,640.00 Frankie Muniz 2000 Mov-6396 My Dog Skip PG 8 USA Action $ 7,791,146.00 Zoey Deutch 2014 Mov-7522 Vampire Academy PG-13 9 USA Crime $ 687,194.00 Samuel L. Jackson 2001 10 USA Comedy $ 9,436,390.00 Alexa PenaVega 2004 Mov-7957 Sleepover PG 11 USA Comedy $ 16,324,573.00 Debbie Harry 1990 Mov-7354 Tales from the Darkside: The Movie IR 12 USA Comedy $ 485,772.00 Ed Begley Jr. 1990 Mov-8908 Meet the Applegates R 13 USA Horror 22,163,442.00 Anna Paquin 2002 Mov-7969 Darkness PG-13 14 USA Adventure $ 12,741,891.00 Andrew McCarthy 1993 Mov-3778 Weekend at Bernie's II PG 15 Japan Animation $ 85,744,662.00 Veronica Taylor 1998 Mov-7718 Pokémon: The First Movie - Mewtwo Strikes Ba G 16 UK Animation $ 6,574,896.00 Michael Crawford 1993 Mov-7744 Once Upon a Forest G 17 USA Drama $ 52,474,616.00 Shia LaBeouf 2010 Mov-4371 Wall Street: Money Never Sleeps 18 USA Action $ 122,012,643.00 Sean Connery 1990 Mov-4246 The Hunt for Red October PG 19 USA Animation $ 161,321,843.00 Ray Romano 2012 Mov-4914 Ice Age: Continental Drift PG 20 UK Biography | $ 30,082,699.00 Cate Blanchett 1998 Mov-6036 Elizabeth R 21 USA Comedy $ 127,352,707.00 Will Ferrell 2013 22 USA Comedy $ 162,001,186.00 Adam Sandler 2010 Mov-6653 Grown Ups PG-13 23 UK Comedy $ 3,916,303.00 Geena Davis 1988 Mov-5175 Earth Girls Are Easy PG 24 USA Comedy $ 17,804,299.00 Drew Barrymore 2010 Mov-4507 Going the Distance R 25 USA Horror $ 40,846,082.00 Geoffrey Rush 1999 Mov-7395 House on Haunted Hill R 26 USA Action $ 1,368,912.00 David Carradine 1986 Mov-7316 Armed Response R 27 UK Adventure $ 1,517,410.00 Pål Sverre Hagen 2012 Mov-8761 Kon-Tiki PG-13 28 USA Comedy $ 38,543,473.00 Jason Segel 2014 29 USA Comedy $ 3,151,130.00 Brian O'Halloran 1994 Mov-8822 Clerks R 30 USA Action $ 155, 247,825.00 Brendan Fraser 1999 Mov-8142 The Mummy PG-13 31 USA Drama $ 39,440,655.00 Cher 2010 Mov-7375 Burlesque PG-13 32 USA Biography $ 14,479,776.00 Amy Adams 2014 Mov-8159 Big Eyes PG-13 33 34 35 .com/danielgrijalvas/movies/data Genre Action Adventure Animation Biography Crime Comedy Drama Horror

Answer 1

Could you please provide more information like how you wanted to use a INDIRECT function ?

Assuming you TABLE1 is name of your name, these two formulas will return the name of the movie with highest gross.

Cell N5 = INDEX(Table1,MATCH(MAX(Table1[Gross]),Table1[Gross],1),7)

Cell P5 =  = INDEX(Table1,MATCH(MAX(Table1[Gross]),Table1[Gross],1),2)

Regards,