I'm trying to use the IF function to create a formula that answers TRUE based on multiple outcomes - I've definitely taken the wrong turn in the code I created:

=IF(E3="S2"OR"S3"OR"S4"OR"S5"OR"S6"OR"S7"OR"S8"OR"S9"OR"S10"OR"S11"OR"S12"OR"S13"OR"S14"OR"S15"OR"S16"OR"S17")

I also want the box to read 90 - e.g. IF the answer is from the above range, the response in the box is 90 rather than true/false.

Lastly, I want to automate this down the column so that any new rows added automatically include the formula.

CodePudding user response：

try:

=IF(REGEXMATCH(QUERY(S2:S17;;9^9); E3); 90; "no match")

but maybe you want this in row 2:

=INDEX(IF(S2:S=E3; 90; ))

CodePudding user response：

Try this:

on S2 paste this formula.

=ArrayFormula(SUBSTITUTE(IFERROR(IF(E2:E="",,VLOOKUP(E2:E,FILTER(E2:E,REGEXEXTRACT(E2:E, "[0-9] ")*1>=2,REGEXEXTRACT(E2:E, "[0-9] ")*1<=17),1,0)),""),ArrayFormula(IFERROR(IF(E2:E="",,VLOOKUP(E2:E,FILTER(E2:E,REGEXEXTRACT(E2:E, "[0-9] ")*1>=2,REGEXEXTRACT(E2:E, "[0-9] ")*1<=17),1,0)),"")),90))