New Contributor

# 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: Y,Y,blank,blank = return a Y

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

 1 A B C D E F 2 3 Y Y Y Y Y 4 Y Y N Y Y 5 N Y NA NA 6 N Y NA NA
6 Replies

# Re: Multiple variables to evaluate a row

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.

# Re: Multiple variables to evaluate a row

As variant

``````=BYCOL(
B2:G6,
LAMBDA(a,
IFERROR(
INDEX(
FILTER(Priority, COUNTIF(a, Priority) > 0),
1
),
""
)
)
)``````

# Re: Multiple variables to evaluate a row

@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;””)

# Re: Multiple variables to evaluate a row

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

# Re: Multiple variables to evaluate a row

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