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))
T开发者_开发技巧his 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?
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.
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.
精彩评论