CODEWITHSUNDEEP
excelexcel-formulaformula
user2411448
user2411448

Reputation:

Maxif equivalent in excel?

Say I have the following table in excel:
enter image description here

How would I go about creating a function that would select the Fruit with longest shelf life? Order is important with this list, so sorting is not an option. The formula also has to be limited to one cell, I cannot drag and drop equations.

Upvotes: 0

Views: 306

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

How about the Array formula:

=INDEX(A1:A100,SUMPRODUCT(--(C1:C100=MAX(IF(B1:B100="fruit",C1:C100,"")))*(B1:B100="fruit")*ROW(1:100)))

enter image description here

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

This is based on:

  • getting the max life for fruits from MAX(IF())
  • getting the row number from SUMPRODUCT()
  • getting the name from INDEX()

Upvotes: 1

Related Questions