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

[hrachao]

Hi again!

[TABLE="width: 609"]
<colgroup><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 8"]My formula isn't working as intended anymore. Here is what I wanted:[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]I have 3 worksheets:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]WS1


[/TD]
[TD][/TD]
[TD]WS2[/TD]
[TD][/TD]
[TD]WS3[/TD]
[TD][/TD]
[TD]WS4[/TD]
[/TR]
[TR]
[TD]Vendor name[/TD]
[TD]Expiry date[/TD]
[TD]Formula field[/TD]
[TD][/TD]
[TD]Vendor name[/TD]
[TD][/TD]
[TD]Vendor name[/TD]
[TD]Vendor name[/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD="align: right"]31-12-2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]Alpha[/TD]
[TD][/TD]
[TD]Alpha[/TD]
[TD]Alpha[/TD]
[/TR]
[TR]
[TD]Beta[/TD]
[TD="align: right"]31-12-2016[/TD]
[TD][/TD]
[TD][/TD]
[TD]Beta[/TD]
[TD][/TD]
[TD]Gama[/TD]
[TD]Gama[/TD]
[/TR]
[TR]
[TD]Delta[/TD]
[TD="align: right"]31-12-2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gama[/TD]
[TD]N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gama[/TD]
[TD]N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 7"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl85, width: 64"] Conditions for formula:[/TD]
[/TR]
[TR]
[TD="class: xl85"]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 ""[/TD]
[/TR]
[TR]
[TD="class: xl85"]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



[/TD]
[/TR]
[TR]
[TD="class: xl85"]I was using:[/TD]
[/TR]
[TR]
[TD="class: xl85"]=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",""),""))

[/TD]
[/TR]
[TR]
[TD="class: xl85"]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[/TD]
[/TR]
</tbody>[/TABLE]

 

[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",""),"")