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:

WS1 WS2 WS3 Vendor name Expiry date Formula field Vendor name Vendor name Alpha 31-12-2018 Alpha Alpha Beta 31-12-2016 Beta Gama Delta 31-12-2017 Gama N/A Gama N/A <colgroup><col><col><col><col><col><col><col></colgroup><tbody> </tbody> <colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody> </tbody>

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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