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]

 

[hrachao]

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

Hi!

I have 4 worksheets:
[TABLE="class: grid, width: 355"]
<tbody>[TR]
[TD="colspan: 3, align: center"]WS1[/TD]
[TD]WS2[/TD]
[TD]WS3[/TD]
[TD]WS4[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD]Vendor name[/TD]
[TD]Expiry date[/TD]
[TD]Formula field[/TD]
[TD]Vendor name[/TD]
[TD]Vendor name[/TD]
[TD]Vendor name[/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD]31-12-2018[/TD]
[TD][/TD]
[TD]Alpha[/TD]
[TD]Alpha[/TD]
[TD]Alpha[/TD]
[/TR]
[TR]
[TD]Beta[/TD]
[TD]31-12-2016[/TD]
[TD][/TD]
[TD]Beta[/TD]
[TD]Gama[/TD]
[TD]Gama[/TD]
[/TR]
[TR]
[TD]Delta[/TD]
[TD]31-12-2017[/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]
[/TR]
[TR]
[TD]Gama[/TD]
[TD]N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Conditions for formula:
If vendor in WS1 is in WS2 OR WS3 OR WS4 AND expiry date is < 31/12/2018, then return text "request form"

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


I was using:
=IF(B1="N/A","",IF(B1 < date(2018,12,31),if(or(not(iserror(search(a1,'ws2'!$d:$d,0))),not(iserror(search(a1,'ws3'!$l:$l,0))),not(iserror(search( a1,'ws4'!$a$1:$a$4184)))),"request="" form",""),""))

<date(2018,12,31),if(or(not(iserror(search(a1,'ws2'!$d:$d,0))),not(iserror(search(a1,'ws3'!$l:$l,0))),not(iserror(search(a1,'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</date(2018,12,31),if(or(not(iserror(search(a1,'ws2'!$d:$d,0))),not(iserror(search(a1,'ws3'!$l:$l,0))),not(iserror(search(a1,'ws4'!$a$1:$a$4184)))),"request>

 

[Special-K99]

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

in Sheet1 (no result cell specified)

=IFERROR(IF(AND(OR(A2=Sheet2!A2,A2=Sheet3!E3,A2=Sheet2!F3),YEAR(B2)<=2018),"Request Form",""),"")

 

[hrachao]

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

in Sheet1 (no result cell specified)

=IFERROR(IF(AND(OR(A2=Sheet2!A2,A2=Sheet3!E3,A2=Sheet4!F3),YEAR(B2)<=2018),"Request Form",""),"")

Hi! Thanks for your input. Something is not working yet. I have a vendor of WS1 present in WS4 with date <31.12.2017 and i get blank return "" instead of "request form". any idea what could be the reason?

 

[Special-K99]

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

"I have a vendor of WS1"

Vendors are Alpha Beta Gamma Delta arent they? There is no vendor with a value of "WS1" is there? if there is your description doesn;t reflect that.

You've now produce THREE different types of date format in your description of the problem 31-12-2018, 31/12/2018 and now 31.12.2017.
So are we talking about text or proper Excel dates?

When you are comparing vendors are you comparing a vendor in a particular cell of other worksheets or the WHOLE COLUMN of the other worksheets? Your table implies it's cell by cell comparison.
But you're description mentions other worksheets. If thats the case you should have provided SEPARATE tables not one merged table.

 

[hrachao]

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

Hi, you are totally right, my input was very faulty... I am having trouble with formatting in this forum. Let me try and remake it, would still love to get some help.

 

[hrachao]

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

I have 4 separate worksheets. Each worksheet has 1 set of information.

The idea is to check if a vendor name (column A) from worksheet 1 is present in any of the other worksheets (also in columns A) - so the formula needs to check the whole column of the other worksheets
Then, if the vendor is indeed present in any of the other worksheets, "request form" should be returned if the date of expiration in column B of worsksheet 1 is <31-12-2018. All dates are properly formatted as date in the worksheet.

Worksheet 1: columns used are A (vendor name) and B (expiration dates). Formula is intended to be put on column C and dragged down

1 31-12-2015
2 31-12-2017
3 31-12-2020
4 N/A
4 N/A
5 31-12-2017

Worsksheets 2, 3 and 4

All vendors are present in column A of the respective worksheet. The whole column should be checked in the formula, as these files are regularly pdated with further rows.

(I changed the vendor names to just simple numbering now)

Is this clear?

 

[Special-K99]

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

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

 

[Firesword]

Hi hrachao
Assuming that the vendor name is in column A on all sheets if not then change to suit, and that you are going to paste this into sheet 1 on cell C2 then try this. I've left the text in so you can see what it is doing, where B2>43465, this is the Microsoft date number for the 31/12/2018.

=IF(IFERROR(INDEX(Sheet2!$A:$A,MATCH(A2,Sheet2!$A:$A,0),1),IFERROR(INDEX(Sheet3!$A:$A,MATCH(A2,Sheet3!$A:$A,0),1),IFERROR(INDEX(Sheet4!$A:$A,MATCH(A2,Sheet4!$A:$A,0),1),"")))=A2,IF(OR(B2="N/A",B2>43465),"N/A or greater than 31/12/18",2),"Not in any worksheet")

 

[Firesword]

sorry that should be paste into sheet1 cell C3, also when pasting that formula in it has added a space on the last IFERRO R, please remove that too