Forum Discussion
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
As variant
=BYCOL( B2:G6, LAMBDA(a, IFERROR( INDEX( FILTER(Priority, COUNTIF(a, Priority) > 0), 1 ), "" ) ) )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.
- 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 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 "" .
