IF Statement with multiple criteria

You could do it like this:

Formula:

=IF(ISERROR(VLOOKUP(IF(ISERROR(A2),IF(ISERROR(B2),IF(ISERROR(C2),D2,C2),B2),A2),CounterParty!B:B,1,0)),"Yes","No")

but as you are only looking at one column you can use MATCH instead of VLOOKUP like this:

Formula:

=IF(ISERROR(MATCH(IF(ISERROR(A2),IF(ISERROR(B2),IF(ISERROR(C2),D2,C2),B2),A2),CounterParty!B:B,0)),"Yes","No")

Hope this helps.

Pete