SOLVED

Formula for selecting text based on conditions

New Contributor

In need of a formula for column C Risk Level which = The text representing the highest risk between Column A Audit Risk Level and Column B Perceived Risk Level

 

For Example

lbrosten_1-1635702685783.png

 

 

7 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@lbrosten 

=IF(OR(A4="High",B4="High"),"High",IF(OR(A4="Medium",B4="Medium"),"Medium","Low"))

 

Is this the formula you are looking for?

Thank you . Almost perfect. It's not working for the scenario where column A is a higher risk than column B. 

lbrosten_0-1635704149542.png

 

@Quadruple_Pawn 

@lbrosten Excel can't really tell that the word "High" reflects a higher risk than the word "Low". Easier to use number values 1,2 and 3 for Low, Medium and High. The formula in C4 then could be =MAX(A4:B4)

@lbrosten 

If add helper range at any place of the workbook like

image.png

formula could be

=INDEX( $H$3:$H$5, MAX( XMATCH(A3:B3,$H$3:$H$5) ) )
Typo on my part. It works perfectly. Thanks for your help!

@lbrosten 

Can you communicate the formula you entered?

The suggested formula works fine in all cases, see attached file.

@lbrosten 

As a 365 beta user, I set out to use @Riny_van_Eekelen's idea of using the MAX function.

image.png

The function first looks up the text levels to turn them into values.  Then to prevent MAX operating over the entire array, I introduced the MAP helper function

 

= LAMBDA(audit,perceived,
    LET(
      av, XMATCH(audit,RiskLevels),
      pv, XMATCH(perceived, RiskLevels),
      CritLevelλ, LAMBDA(a,p, MIN(a,p)),
      combined,   MAP(av,pv,CritLevelλ),
      INDEX(RiskLevels, combined)
    )
  )

 

to run through the arrays term by term.  At present, such solutions are so unfamiliar that I have yet to come to a firm view as to their being over-complicated or just clear statements of fact.