SOLVED

Formula for selecting text based on conditions

%3CLINGO-SUB%20id%3D%22lingo-sub-2906066%22%20slang%3D%22en-US%22%3EFormula%20for%20selecting%20text%20based%20on%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2906066%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20need%20of%20a%20formula%20for%20column%20C%20%3CEM%3ERisk%20Level%3C%2FEM%3E%20which%20%3D%20The%20text%20representing%20the%20highest%20risk%20between%20Column%20A%20%3CEM%3EAudit%20Risk%20Level%3C%2FEM%3E%20and%20Column%20B%20%3CEM%3EPerceived%20Risk%20Level%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EFor%20Example%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22lbrosten_1-1635702685783.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F321962i5D76B1A2F7C931F0%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22lbrosten_1-1635702685783.png%22%20alt%3D%22lbrosten_1-1635702685783.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2906066%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2906109%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20selecting%20text%20based%20on%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2906109%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1201369%22%20target%3D%22_blank%22%3E%40lbrosten%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(OR(A4%3D%22High%22%2CB4%3D%22High%22)%2C%22High%22%2CIF(OR(A4%3D%22Medium%22%2CB4%3D%22Medium%22)%2C%22Medium%22%2C%22Low%22))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20the%20formula%20you%20are%20looking%20for%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2906131%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20selecting%20text%20based%20on%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2906131%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20.%20Almost%20perfect.%20It's%20not%20working%20for%20the%20scenario%20where%20column%20A%20is%20a%20higher%20risk%20than%20column%20B.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22lbrosten_0-1635704149542.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F321963iCB395937E819F2FE%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22lbrosten_0-1635704149542.png%22%20alt%3D%22lbrosten_0-1635704149542.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1174419%22%20target%3D%22_blank%22%3E%40Quadruple_Pawn%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2906138%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20selecting%20text%20based%20on%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2906138%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1201369%22%20target%3D%22_blank%22%3E%40lbrosten%3C%2FA%3E%26nbsp%3BExcel%20can't%20really%20tell%20that%20the%20word%20%22High%22%20reflects%20a%20higher%20risk%20than%20the%20word%20%22Low%22.%20Easier%20to%20use%20number%20values%201%2C2%20and%203%20for%20Low%2C%20Medium%20and%20High.%20The%20formula%20in%20C4%20then%20could%20be%20%3CEM%3E%3CSTRONG%3E%3DMAX(A4%3AB4)%3C%2FSTRONG%3E%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2906143%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20selecting%20text%20based%20on%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2906143%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1201369%22%20target%3D%22_blank%22%3E%40lbrosten%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20add%20helper%20range%20at%20any%20place%20of%20the%20workbook%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20575px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F321964i09AD4752F61FFBBB%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eformula%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DINDEX(%20%24H%243%3A%24H%245%2C%20MAX(%20XMATCH(A3%3AB3%2C%24H%243%3A%24H%245)%20)%20)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2906154%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20selecting%20text%20based%20on%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2906154%22%20slang%3D%22en-US%22%3ETypo%20on%20my%20part.%20It%20works%20perfectly.%20Thanks%20for%20your%20help!%3C%2FLINGO-BODY%3E
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.