How can retrieve cell address that match criteria?

Here's an ugly solution. Just MATCH the result of the formula you have in the same conditional array you passed to the MAX function. Then pass the resulting index number to the ADDRESS function.

=ADDRESS(1+MATCH(MAX(IF($A$2:$A$100=$G$2,$B$2:$B$100)),IF($A$2:$A$100=$G$2,$B$2:$B$100),0),2)

Enter as an array formula with Ctrl+Shift+Enter.

There's probably a better way to go about this, but this was my first thought.

Excellll

Posted 2014-07-10T16:38:16.047

Reputation: 11 857

Excellent. There is not a ugly solution if it works. – Hose – 2014-07-10T18:02:17.683

+1! I agree. This is not ugly; it’s what Excel forces you to do. I was working on something similar, but yours is more elegant and you got it in faster. A couple of notes: (1) If this formula goes into cell H2, and there are other product names in G3, G4, …, then your references to $G$2 should be changed to $G2 or just G2. (2) To increase portability/flexibility, you might replace the 1 with ROW($A$2)-1 (e.g., in case there’s another batch of data in rows 102-200). – Scott – 2014-07-10T18:06:44.393

How can retrieve cell address that match criteria?

Answers