IFS test Array

Try wrapping LOOKUP around your IFS formula.

Formula:

Please Login or Register  to view this content.

Although this is all around easier.

Formula:

Please Login or Register  to view this content.

Try Reverse lookup:

Please Login or Register  to view this content.

You don't need IFS. If you're really using Excel 2019, this should work.

G1: =IFS(OR(A1=$O$2:$O$8),$O$1,OR(A1=$P$2:$P$8),$P$1,OR(A1=$Q$2:$Q$8),$Q$1,OR(A1=$R$2:$R$6),$R$1)

This is a little inefficient. More efficient but longer would be

G1: =IFERROR($O$1/SIGN(MATCH(A1,$O$2:$O$8,0)),IFERROR($P$1/SIGN(MATCH(A1,$P$2:$P$8,0)),IFERROR($Q$1/SIGN(MATCH(A1,$Q$2:$Q$8,0)),
IFERROR($R$1/SIGN(MATCH(A1,$R$2:$R$6,0)),"--"))))

ADDED: Had to see how spilled formulas/dynamic arrays could handle this.

G1: =INDEX($O$1:$R$1,1+MOD(MATCH(A1,INDEX($O$2:$R$8,1+MOD(SEQUENCE(26,1,0),7),1+INT(SEQUENCE(26,1,0)/7)),0)-1,4))

This should be more efficient than lots of OR calls. The inner INDEX call reshapes O2:R8 into a 26-row x 1-column array, then matches against that.

You are welcome. Glad to help, and thank you for the feedback added rep and marking your thread Solved.