Help - lost in big formula

[hrachao]

Hi everyone,

I got lost trying to do a formula... Hope you can help me!
I have 3 worksheets:

[TABLE="width: 512"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 3"][TABLE="width: 606"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 512"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 3"]WS1[/TD]
[TD][/TD]
[TD]WS2[/TD]
[TD][/TD]
[TD]WS3[/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]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD]31-12-2018[/TD]
[TD] [/TD]
[TD][/TD]
[TD]Alpha[/TD]
[TD][/TD]
[TD]Alpha[/TD]
[/TR]
[TR]
[TD]Beta[/TD]
[TD]31-12-2016[/TD]
[TD] [/TD]
[TD][/TD]
[TD]Beta[/TD]
[TD][/TD]
[TD]Gama[/TD]
[/TR]
[TR]
[TD]Delta[/TD]
[TD]31-12-2017[/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]
[/TR]
[TR]
[TD]Gama[/TD]
[TD]N/A[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Conditions for formula
If vendor in WS1 is not in WS2 nor WS3, then return blank ""
If vendor in WS1 has N/A expiry date, then return blank ""
if vendor in WS1 is in WS2 OR WS3, then return text "request form" ONLY IF the expiry date is < 31/12/2018

Thx!
Helena

 

[alansidman]

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

What result if the above statement is false, ie. date is >31/12/2018

 

[Norie]

Assuming Vendor Name is in column A on all the sheets and Expiry Date is in column B on WS1.

=IF(B2="N/A","",IF(B2 < DATE(2018,12,31),IF(OR(NOT(ISERROR(MATCH(A2,'WS2'!A:A,0))),NOT(ISERROR(MATCH(A2,'WS3'!A:A,0)))),"Request Form",""),""))

 

[hrachao]

Ah right!

If date is >31/12/2018, then return blank ""

 

[hrachao]

This worked, thank you!

Assuming Vendor Name is in column A on all the sheets and Expiry Date is in column B on WS1.

=IF(B2="N/A","",IF(B2 < DATE(2018,12,31),IF(OR(NOT(ISERROR(MATCH(A2,'WS2'!A:A,0))),NOT(ISERROR(MATCH(A2,'WS3'!A:A,0)))),"Request Form",""),""))