 SOLVED

# Formula for selecting text based on conditions

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

# Re: Formula for selecting text based on conditions

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

Is this the formula you are looking for?

# Re: Formula for selecting text based on conditions

Thank you . Almost perfect. It's not working for the scenario where column A is a higher risk than column B. # Re: Formula for selecting text based on conditions

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

# Re: Formula for selecting text based on conditions

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

# Re: Formula for selecting text based on conditions

Typo on my part. It works perfectly. Thanks for your help!

# Re: Formula for selecting text based on conditions

Can you communicate the formula you entered?

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

# Re: Formula for selecting text based on conditions

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.