Discussion Re: Formula for selecting text based on conditions in Excel
https://techcommunity.microsoft.com/t5/excel/formula-for-selecting-text-based-on-conditions/m-p/2906109#M120329
<P><LI-USER uid="1201369"></LI-USER> </P><P>=IF(OR(A4="High",B4="High"),"High",IF(OR(A4="Medium",B4="Medium"),"Medium","Low"))</P><P> </P><P>Is this the formula you are looking for?</P>Sun, 31 Oct 2021 18:03:35 GMTQuadruple_Pawn2021-10-31T18:03:35ZFormula for selecting text based on conditions
https://techcommunity.microsoft.com/t5/excel/formula-for-selecting-text-based-on-conditions/m-p/2906066#M120324
<P>In need of a formula for column C <EM>Risk Level</EM> which = The text representing the highest risk between Column A <EM>Audit Risk Level</EM> and Column B <EM>Perceived Risk Level</EM></P><P> </P><P><EM>For Example</EM></P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="lbrosten_1-1635702685783.png" style="width: 400px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/321962i5D76B1A2F7C931F0/image-size/medium?v=v2&px=400" role="button" title="lbrosten_1-1635702685783.png" alt="lbrosten_1-1635702685783.png" /></span></P><P> </P><P> </P>Sun, 31 Oct 2021 17:52:01 GMThttps://techcommunity.microsoft.com/t5/excel/formula-for-selecting-text-based-on-conditions/m-p/2906066#M120324lbrosten2021-10-31T17:52:01ZRe: Formula for selecting text based on conditions
https://techcommunity.microsoft.com/t5/excel/formula-for-selecting-text-based-on-conditions/m-p/2906109#M120329
<P><LI-USER uid="1201369"></LI-USER> </P><P>=IF(OR(A4="High",B4="High"),"High",IF(OR(A4="Medium",B4="Medium"),"Medium","Low"))</P><P> </P><P>Is this the formula you are looking for?</P>Sun, 31 Oct 2021 18:03:35 GMThttps://techcommunity.microsoft.com/t5/excel/formula-for-selecting-text-based-on-conditions/m-p/2906109#M120329Quadruple_Pawn2021-10-31T18:03:35ZRe: Formula for selecting text based on conditions
https://techcommunity.microsoft.com/t5/excel/formula-for-selecting-text-based-on-conditions/m-p/2906131#M120331
<P>Thank you . Almost perfect. It's not working for the scenario where column A is a higher risk than column B. </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="lbrosten_0-1635704149542.png" style="width: 400px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/321963iCB395937E819F2FE/image-size/medium?v=v2&px=400" role="button" title="lbrosten_0-1635704149542.png" alt="lbrosten_0-1635704149542.png" /></span></P><P> </P><P><LI-USER uid="1174419"></LI-USER> </P>Sun, 31 Oct 2021 18:17:07 GMThttps://techcommunity.microsoft.com/t5/excel/formula-for-selecting-text-based-on-conditions/m-p/2906131#M120331lbrosten2021-10-31T18:17:07ZRe: Formula for selecting text based on conditions
https://techcommunity.microsoft.com/t5/excel/formula-for-selecting-text-based-on-conditions/m-p/2906138#M120334
<P><LI-USER uid="1201369"></LI-USER> 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 <EM><STRONG>=MAX(A4:B4)</STRONG></EM></P>Sun, 31 Oct 2021 18:25:14 GMThttps://techcommunity.microsoft.com/t5/excel/formula-for-selecting-text-based-on-conditions/m-p/2906138#M120334Riny_van_Eekelen2021-10-31T18:25:14ZRe: Formula for selecting text based on conditions
https://techcommunity.microsoft.com/t5/excel/formula-for-selecting-text-based-on-conditions/m-p/2906143#M120335
<P><LI-USER uid="1201369"></LI-USER> </P>
<P>If add helper range at any place of the workbook like</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 575px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/321964i09AD4752F61FFBBB/image-size/large?v=v2&px=999" role="button" title="image.png" alt="image.png" /></span></P>
<P>formula could be</P>
<LI-CODE lang="excel-formula">=INDEX( $H$3:$H$5, MAX( XMATCH(A3:B3,$H$3:$H$5) ) )</LI-CODE>Sun, 31 Oct 2021 18:31:25 GMThttps://techcommunity.microsoft.com/t5/excel/formula-for-selecting-text-based-on-conditions/m-p/2906143#M120335Sergei Baklan2021-10-31T18:31:25ZRe: Formula for selecting text based on conditions
https://techcommunity.microsoft.com/t5/excel/formula-for-selecting-text-based-on-conditions/m-p/2906154#M120337
Typo on my part. It works perfectly. Thanks for your help!Sun, 31 Oct 2021 18:34:10 GMThttps://techcommunity.microsoft.com/t5/excel/formula-for-selecting-text-based-on-conditions/m-p/2906154#M120337lbrosten2021-10-31T18:34:10ZRe: Formula for selecting text based on conditions
https://techcommunity.microsoft.com/t5/excel/formula-for-selecting-text-based-on-conditions/m-p/2906155#M120338
<P><LI-USER uid="1201369"></LI-USER> </P><P>Can you communicate the formula you entered?</P><P>The suggested formula works fine in all cases, see attached file.</P>Sun, 31 Oct 2021 18:34:45 GMThttps://techcommunity.microsoft.com/t5/excel/formula-for-selecting-text-based-on-conditions/m-p/2906155#M120338Quadruple_Pawn2021-10-31T18:34:45ZRe: Formula for selecting text based on conditions
https://techcommunity.microsoft.com/t5/excel/formula-for-selecting-text-based-on-conditions/m-p/2906467#M120350
<P><LI-USER uid="1201369"></LI-USER> </P><P>As a 365 beta user, I set out to use <LI-USER uid="403176"></LI-USER>'s idea of using the MAX function.</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 684px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/321985i338416C615B12228/image-size/large?v=v2&px=999" role="button" title="image.png" alt="image.png" /></span></P><P>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</P><P> </P><LI-CODE lang="excel-formula">= 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)
)
)</LI-CODE><P> </P><P>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.</P>Sun, 31 Oct 2021 21:53:40 GMThttps://techcommunity.microsoft.com/t5/excel/formula-for-selecting-text-based-on-conditions/m-p/2906467#M120350Peter Bartholomew2021-10-31T21:53:40Z