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




7 Replies
best response confirmed by allyreckerman (Microsoft)




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 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)


If add helper range at any place of the workbook like


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!


Can you communicate the formula you entered?

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


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


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