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”.

  • How to convert and compare a date string to a date in Excel
  • Download excel file from page via WebApi call
  • Multiple If function with priority
  • Excel VBA - Dictionary - storing and retrieving values
  • Excel macro copy values from one row to another without overwriting formulas in the target row
  • Compare 2 sql tables for data that does not match
  • 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?

  • excel vba arrayformula with filtered range too long
  • Formula to search table and return multiple column headers
  • Excel - (Array Formula) Sum IF Multiple Criteria with a Difference
  • how to return an array based on conditions in excel?
  • Array formula to show value if it does not match
  • Find four adjacent empty cells in Excel
  • 2 Solutions collect form web for “MaxIf with associated row info?”

    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.

    MS Excel Spreadsheet is the best Office Software, Excel VBA and Excel Formulas make Spreadsheet work faster.