Formula not working as intended - also open to new way of achieving result

[hrachao]

Hi again!

My formula isn't working as intended anymore. Here is what I wanted:
I have 3 worksheets:
WS1		WS2		WS3		WS4
Vendor name	Expiry date	Formula field		Vendor name		Vendor name	Vendor name
Alpha	31-12-2018			Alpha		Alpha	Alpha
Beta	31-12-2016			Beta		Gama	Gama
Delta	31-12-2017
Gama	N/A
Gama	N/A

<colgroup><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

Conditions for formula:

If vendor in WS1 is not in WS2 nor WS3 nor WS4, then return blank "" if vendor in WS1 is in WS2 OR WS3 OR WS but the expiry date is "N/A" or > 31/12/2018, then return blank ""

if vendor in WS1 is in WS2 OR WS3 OR WS4, then return text "request form" ONLY IF the expiry date is < 31/12/2018

I was using:

=IF(B3="N/A","",IF(B3<DATE(2018,12,31),IF(OR(NOT(ISERROR(MATCH(A3'[WS2]'!$D:$D,0))),NOT(ISERROR(MATCH(A3,'[WS3]'!$L:$L,0))),NOT(ISERROR(MATCH(A3,'WS4'!$A$1:$A$4184)))),"Request Form",""),""))

But with this formula, if the expiry date is <31/12/2018 it gives me "request form", even if the vendor name is not present in other worksheet Thx for your help!! Rgds, Helena

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Fluff]

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.(rule 12 here: Forum Rules).

I have merged the two threads

 

[hrachao]

Re: Help with multiple conditions (if, OR, match/search, dates)

Thanks, works like a charm!

Also thanks to Firesword!

Try

=IFERROR(IF(AND((COUNTIF(Sheet2!A1:A1000,A2)+COUNTIF(Sheet3!A1:A1000,A2)+COUNTIF(Sheet4!A1:A1000,A2))>0,YEAR(B2)<=2018),"Request Form",""),"")