Oct 31 2021 10:52 AM
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
Oct 31 2021 11:03 AM
Solution=IF(OR(A4="High",B4="High"),"High",IF(OR(A4="Medium",B4="Medium"),"Medium","Low"))
Is this the formula you are looking for?
Oct 31 2021 11:17 AM
Thank you . Almost perfect. It's not working for the scenario where column A is a higher risk than column B.
Oct 31 2021 11:25 AM
@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)
Oct 31 2021 11:31 AM
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) ) )
Oct 31 2021 11:34 AM
Oct 31 2021 11:34 AM
Can you communicate the formula you entered?
The suggested formula works fine in all cases, see attached file.
Oct 31 2021 02:48 PM - edited Oct 31 2021 02:53 PM
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,
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.
Oct 31 2021 11:03 AM
Solution=IF(OR(A4="High",B4="High"),"High",IF(OR(A4="Medium",B4="Medium"),"Medium","Low"))
Is this the formula you are looking for?