Forum Discussion

talotaibi's avatar
talotaibi
Copper Contributor
Jul 21, 2023
Solved

Searching for a formula to select the highest value

I need help creating a formula in Excel, as I have four values in the left column (Minor, Moderate, Major, Extreme) and on the right column, it should show in the cell of the highest value from the l...
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Jul 21, 2023

    talotaibi 

    =IF(COUNTIF(I2:I5,"extreme"),"extreme",IF(COUNTIF(I2:I5,"major"),"major",IF(COUNTIF(I2:I5,"moderate"),"moderate",IF(COUNTIF(I2:I5,"minor"),"minor",""))))

    Does this return the intended result? 

Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
Jul 22, 2023

talotaibi 

As an alternative:

=LET(
a,{"Minor","Moderate","Major","Extreme"},
INDEX(a,MAX(MATCH(A2:A5,a,0))))
    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Jul 22, 2023

      talotaibi 

      I suspect the reason is that you do not have access to Excel 365.  Taken to the extreme 365 solutions bear little similarity to traditional spreadsheet solutions.  For example

      = LET(
      scale, {"Minor";"Moderate";"Major";"Extreme"},
      TAKE(FILTER(scale, COUNTIFS(rate, scale)),-1)
  )

      sets up the scale and counts the number of occurrences for each level.  Then filtering out levels that do not occur and returning the final value gives the desired result.

       

Resources