Multiple variables to evaluate a row

%3CLINGO-SUB%20id%3D%22lingo-sub-3190477%22%20slang%3D%22en-US%22%3EMultiple%20variables%20to%20evaluate%20a%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3190477%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20a%20formula%20hat%20evaluates%20the%20cells%20in%20the%20column%20below%20it%20and%20returns%20the%20answer%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%3A%26nbsp%3B%20Y%2CY%2CN%2CN%20%3D%20return%20a%20N%3C%2FP%3E%3CP%3EB%3A%20Y%2CY%2CY%2CY%20%3D%20return%20a%20Y%3C%2FP%3E%3CP%3EC%3A%20Y%2CN%2CNA%2CNA%20%3D%20return%20a%20N%3C%2FP%3E%3CP%3E%3CLI-EMOJI%20id%3D%22lia_anguished-face%22%20title%3D%22%3Aanguished_face%3A%22%3E%3C%2FLI-EMOJI%3E%20Y%2CY%2CNA%2CNA%20%3D%20return%20a%20Y%3C%2FP%3E%3CP%3EE%3A%20Y%2CY%2Cblank%2Cblank%20%3D%20return%20a%20Y%3C%2FP%3E%3CP%3EF%3A%26nbsp%3B%20all%20blanks%20%3D%3D%20don't%20calculate%2C%20leave%20the%20cell%20blank%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22280px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2240px%22%20height%3D%2259px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2259px%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2259px%22%3EB%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2259px%22%3EC%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2259px%22%3ED%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2259px%22%3EE%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3EF%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%20height%3D%2230px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%20height%3D%2230px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2230px%22%3EY%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2230px%22%3E%3CP%3EY%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2230px%22%3EY%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2230px%22%3E%3CP%3EY%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2230px%22%3EY%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%20height%3D%2230px%22%3E4%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2230px%22%3EY%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2230px%22%3EY%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2230px%22%3EN%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2230px%22%3EY%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2230px%22%3EY%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%20height%3D%2230px%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2230px%22%3EN%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2230px%22%3EY%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2230px%22%3ENA%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2230px%22%3ENA%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%20height%3D%2230px%22%3E6%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2230px%22%3EN%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2230px%22%3EY%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2230px%22%3ENA%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2230px%22%3ENA%3C%2FTD%3E%3CTD%20width%3D%2240px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3190477%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3191179%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20variables%20to%20evaluate%20a%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3191179%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1313009%22%20target%3D%22_blank%22%3E%40Julie1352%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20can%20be%20done%20using%20IFS%3B%3C%2FP%3E%3CP%3Exmatch%20%22N%22%20in%20B2%3AB7.%20If%20it%20is%20not%20error%2C%20then%20show%20N.%20Otherwise%2C%20do%20the%20same%20test%20with%20Y%20and%20so%20on.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22bosinander_0-1645421577207.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F349842iE8BB637A5E0FF833%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22bosinander_0-1645421577207.png%22%20alt%3D%22bosinander_0-1645421577207.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20actual%20formula%20becomes%20quite%20long%20but%20is%20merely%20repeating%20itself.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIFS(%0A%20%20NOT(ISERROR(XMATCH(%24K%242%3BB2%3AB7)))%3B%24K%242%3B%0A%20%20NOT(ISERROR(XMATCH(%24K%243%3BB2%3AB7)))%3B%24K%243%3B%0A%20%20NOT(ISERROR(XMATCH(%24K%244%3BB2%3AB7)))%3B%24K%244%3B%0A%20%20TRUE%3B%22%22%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20last%20used%20'condition'%20is%20simply%20true%26nbsp%3B%3CSPAN%3Eso%20that%20if%20nothing%20before%20has%20been%20true%2C%20that%20one%20is%20and%20return%20an%20empty%20string%20leaving%20the%20cell%20blank.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3192013%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20variables%20to%20evaluate%20a%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3192013%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1313009%22%20target%3D%22_blank%22%3E%40Julie1352%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DBYCOL(%0A%20%20%20%20%20%20%20%20B2%3AG6%2C%0A%20%20%20%20%20%20%20%20LAMBDA(a%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20IFERROR(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20INDEX(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20FILTER(Priority%2C%20COUNTIF(a%2C%20Priority)%20%26gt%3B%200)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%201%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22%22%0A%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3192953%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20variables%20to%20evaluate%20a%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3192953%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1159986%22%20target%3D%22_blank%22%3E%40bosinander%3C%2FA%3E%20Thank%20You.%20Would%20you%20mind%20looking%20at%20my%20formula%3F%20I've%20replaced%20the%20cells%20but%20there%20is%20an%20error.%3CBR%20%2F%3E%3DIFS(NOT(ISERROR(XMATCH(%24R%242%3BF9%3AF12)))%3B%24R%242%3BNOT(ISERROR(XMATCH(%24R%243%3BF9%3AF12)))%3B%24R%243%3BNOT(ISERROR(XMATCH(%24R%244%3BF9%3AF12)))%3B%24R%244%3BTRUE%3B%E2%80%9D%E2%80%9D)%3C%2FLINGO-BODY%3E
New Contributor

I need a formula hat evaluates the cells in the column below it and returns the answer

 

A:  Y,Y,N,N = return a N

B: Y,Y,Y,Y = return a Y

C: Y,N,NA,NA = return a N

Y,Y,NA,NA = return a Y

E: Y,Y,blank,blank = return a Y

F:  all blanks == don't calculate, leave the cell blank

 

1ABCDEF
2      
3Y

Y

Y

Y

Y 
4YYNYY 
5NYNANA  
6NYNANA  
6 Replies

Hi @Julie1352 

It can be done using IFS;

xmatch "N" in B2:B7. If it is not error, then show N. Otherwise, do the same test with Y and so on.

 

bosinander_0-1645421577207.png

 

The actual formula becomes quite long but is merely repeating itself.

 

=IFS(
  NOT(ISERROR(XMATCH($K$2;B2:B7)));$K$2;
  NOT(ISERROR(XMATCH($K$3;B2:B7)));$K$3;
  NOT(ISERROR(XMATCH($K$4;B2:B7)));$K$4;
  TRUE;""
)

 

The last used 'condition' is simply true so that if nothing before has been true, that one is and return an empty string leaving the cell blank.

@Julie1352 

As variant

=BYCOL(
        B2:G6,
        LAMBDA(a,
            IFERROR(
                INDEX(
                    FILTER(Priority, COUNTIF(a, Priority) > 0),
                    1
                ),
                ""
            )
        )
    )
@bosinander Thank You. Would you mind looking at my formula? I've replaced the cells but there is an error.
=IFS(NOT(ISERROR(XMATCH($R$2;F9:F12)));$R$2;NOT(ISERROR(XMATCH($R$3;F9:F12)));$R$3;NOT(ISERROR(XMATCH($R$4;F9:F12)));$R$4;TRUE;””)

@Julie1352 Making the formula easier to read by entering alt+Enter to get multiple rows in the formula, it looks good

=IFS(
  NOT(ISERROR(XMATCH($R$2;F9:F12)));$R$2;
  NOT(ISERROR(XMATCH($R$3;F9:F12)));$R$3;
  NOT(ISERROR(XMATCH($R$4;F9:F12)));$R$4;
  TRUE;””
)

It is also functional after I redesign the attached example.

Maybe you have an error in a cell, part of the calculation? Or... an excel version not aware of these functions, but then it should have been something like _xlfcn in your formula.

@Julie1352 Your formula works well as long is there is at least one Y, N or NA in the cells checked. But if they are all blank it produces a #NAME? error because of the italic quotation marks in the end. Replace ”” by "" .