CODEWITHSUNDEEP
excelarray-formulas
Chris Trombley
Chris Trombley

Reputation: 2322

MaxIf with associated row info?

Im using array formulas to determine the maximum value of a specific subset of row data:

=MAX(($A2:$A100="somestring")*($C2:$C100))

This works fine & gets me the maximum value in C where A = "somestring".

Now, I want to return other column values associated with this "max" row that are strings. Intuitively I think I may need to ditch the boolean logic multiplication strategy since string values are getting involved. What's the best/cleanest way to go about this?

Upvotes: 1

Views: 2134

Answers (2)

Excellll
Excellll

Reputation: 5785

Try this:

=INDEX(B1:B100,MATCH(MAX((A1:A100="somestring")*(C1:C100)),(A1:A100="somestring")*(C1:C100),0))

Column you want values for is set to B in this example.

Upvotes: 1

Doug Glancy
Doug Glancy

Reputation: 27478

Assuming you're interested in the string in column D, this will work:

=INDEX(($D$1:$D$100),MAX(($A2:$A100="somestring")*(ROW($C2:$C100))))

Note that since it's Indexing on the Row() you're Index column needs to start at 1, e.g., D1:D100.

Upvotes: 1

Related Questions