how to add OR condition to this formula

[Flora20]

How to add Or condition to this formula? i have this formula that works =INDEX('Sheet2'!A:A,MATCH(0,COUNTIF('Sheet1'!$A$2:A2,'Sheet2'!A:A)+IF('Sheet2'!B:B<>"Must",1,0),0))

when i inclue IF('Sheet2'!B:B<>"Shall",1,0) put this OR condition that if it is Must or Shall then include. as this = INDEX('Sheet2'!A:A,MATCH(0,COUNTIF('Sheet1'!$A$2:A2,'Sheet2'!A:A)+IF('Sheet2'!B:B<>"Must",1,0)+IF('Sheet2'!B:B<>"Shall",1,0),0)) it does not work.

what am i missing?

[xlnitwit]

Perhaps this
=INDEX('Sheet2'!A:A,MATCH(0,COUNTIF('Sheet1'!$A$2:A2,'Sheet2'!A:A)+IF('Sheet2'!B:B<>"Must",IF('Sheet2'!B:B<>"Shall"1,0),0),0))

[Flora20]

Thank you very much

[Flora20]

somehow it worked in small data set, but failed on my actual data.

[xlnitwit]

I couldn't say why that might be- most likely data issues. I would note though that using entire columns in an array formula is not a good idea.

[Flora20]

thanks very much. i converted it to table and it worked.

you are a genius!