Index Match Two Criteria With Max Value

Question

Let's say I have a three column data:

Value | Status | Name

I need a formula that gives the name where row has the highest Value and Status is "Done". I have this so far:

=INDEX(Table[Name],MATCH(MAX(Table[Value]),Table[Value],0))

Answer 1

This is an option that hides the array function. I was actually surprised how similar it was to Scott's answer when I was done. I was basically bored out of my mind and needed something to do so I came up with the following to demonstrate you have option on how to achieve what you want.

=INDEX(ScottsTable[Name],MATCH(AGGREGATE(14,6,(ScottsTable[Status]="Done")*ScottsTable[Value],1),ScottsTable[Value],0))

First time for me playing with "Tables". It was interesting watching the cell references change.

Proof of Concept - Tanks to Scott

_{Please give any positive credit for this answer to Scott above.}

Answer 2

You will need an array formula:

=INDEX(Table[Name],MATCH(1,INDEX((MAX(IF(Table[Status]="Done",Table[Value]))=Table[Value])*(Table[Status]="Done"),),0))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting Edit mode. If done correctly then Excel will put {} around the formula.