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.

Julie1352's avatar
Julie1352
Copper Contributor
Feb 21, 2022
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;ā€ā€)
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Feb 21, 2022

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

     

  • bosinander's avatar
    bosinander
    Iron Contributor
    Feb 21, 2022

    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.

Resources