Multiple variables to evaluate a row

Copper 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

D: 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 "" .