Nesting a lookup function in IFS function

[witty_witwit]

Hi!
I have a large dataset and trying to detect specific patterns of numerical sequence. the first 8 logical tests in the image are to return a value if true -basic IFS function stuff- the tricky part is that there are four patterns that if found would need a further lookup down the array to see if there’s a 0 in order to determine which value to return, I tried to nest LOOKUP’s or XMATCH functions but they return #N/A error value instead of the “if false” value of the IF statement. Thoughts as of how can I get this to work or possible alternatives?

Thanks!

 

[SanjayGMusafir]

Hi!
I have a large dataset and trying to detect specific patterns of numerical sequence. the first 8 logical tests in the image are to return a value if true -basic IFS function stuff- the tricky part is that there are four patterns that if found would need a further lookup down the array to see if there’s a 0 in order to determine which value to return, I tried to nest LOOKUP’s or XMATCH functions but they return #N/A error value instead of the “if false” value of the IF statement. Thoughts as of how can I get this to work or possible alternatives?

Thanks!

Instead of HLOOKUP can we try

COUNTIFS(EP2:$FE2),0)<16

 

[witty_witwit]

A good idea! but I ended up doing an IF-OR statement including the next five cells i.e. -
OR(AND(EL19=2,EM19=1,EN19=0,EO19=-1),AND(EL19=1,EM19=0,EN19=-1,EO19=-2)),IF(OR(EP19=0,EQ19=0,ER19=0,ES19=0,ET19=0),"Rv-A-W","Rm-A-W")

A bit basic and longer but it got the job done

Thanks for replying!