CODEWITHSUNDEEP
excelexcel-formula
Timo Krall
Timo Krall

Reputation: 147

Wrong address returned for Index Match Max If Function

I'd like to return the address of the cell with the highest score in the current period. It should be D4, but the formula returns D3.

Any ideas how to fix the formula?

=CELL("ADDRESS";INDEX(Table6[Score];MATCH(MAX(IF(Table6[Period]=D8;Table6[Score]));Table6[Score];0)))

Problem illustration

Upvotes: 0

Views: 321

Answers (1)

C Dieguez
C Dieguez

Reputation: 342

You'll have to break up your calculation into two steps. First, find the largest score for the given period using either your MAX() array function, or if you're using Excel 2016, the built in MAXIFS() function. Next, add a new column to the beginning of your table that concatenates the date and score to create a unique ID. Then, you can use the CELL() function with index and match on that new column.

enter image description here

"Largest Score":

=MAXIFS(Table5[Score],Table5[Period],D8)

"Cell Address":

=CELL("address",INDEX(Table5[Score],MATCH(D8&D9,Table5[Lookup ID],0),1))

Upvotes: 1

Related Questions