Forum Discussion

Julie1352's avatar
Julie1352
Copper Contributor
Feb 20, 2022

Multiple variables to evaluate a row

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: ...
excel
bosinander's avatar
bosinander
Iron Contributor
Feb 21, 2022

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.

 

 

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.

Resources