Complex Array Formula

  • Thread starter Thread starter abc2002
  • Start date Start date
A

abc2002

Hi, this is my first post. I hope that you can help me with my problem.

Explanation:
I have 2 sheets:
-Sheet1
-Sheet2

Sheet1 contains theses columns:
C Type: Text
D Type: Text
E Type: Text
G Type: Text
I Type: Date

Sheet2 contains theses columns:
A Type: Text
C Type: Text
B Type: Text
M Type: Text
F Type: Date

In the sheet2 in the Cell O4, I want to know if the value of A4, C4
B4, M4 are in Sheet1 and the Date difference of F4 is less or equal
days...

**********
So I want to know if some values of the row 4 (Sheet2) match to
SPECIFIC row in Sheet1. In another words, I want to check if the row
of Sheet2 are in Sheet1.
**********

Here is the Array Formula in O4 (Sheet2):

=IF('Sheet1'!$C$2:$C$738=$A4,
IF('Sheet1'!$D$2:$D$738=$C4,
IF('Sheet1'!$E$2:$E$738=$B4,
IF('Sheet1'!$G$2:$G$738=$M4,
IF(IF(ISERROR(DATEDIF('Sheet1'!$I$2:$I$738,$F4,"d")),
DATEDIF($F4,'Sheet1'!$I$2:$I$738,"d"),
DATEDIF('Sheet1'!$I$2:$I$738,$F4,"d"))<=3,1,0)))))

Same formula with comments:
=IF('Sheet1'!$C$2:$C$738=$A4, //If true another if...
IF('Sheet1'!$D$2:$D$738=$C4, //If true another if...
IF('Sheet1'!$E$2:$E$738=$B4, //If true another if...
IF('Sheet1'!$G$2:$G$738=$M4, //If true another if...

//The date diff could be 3 days before or after the date in F4
IF(IF(ISERROR(DATEDIF('Sheet1'!$I$2:$I$738,$F4,"d")),

//Date in F4 is before the date in the sheet1...
DATEDIF($F4,'Sheet1'!$I$2:$I$738,"d"),

//Date in F4 is after the date in sheet1...
DATEDIF('Sheet1'!$I$2:$I$738,$F4,"d")

//If the result of DateDif is 3 or less, then I want to display a wor
in the cell...
)<=3,"OK","NO")))))


I always get FALSE in the cells???
Could you explain to me what is wrong in my function?

Thank
 
...
...
In the sheet2 in the Cell O4, I want to know if the value of A4, C4,
B4, M4 are in Sheet1 and the Date difference of F4 is less or equal 3
days... ...
Here is the Array Formula in O4 (Sheet2):

=IF('Sheet1'!$C$2:$C$738=$A4,
IF('Sheet1'!$D$2:$D$738=$C4,
IF('Sheet1'!$E$2:$E$738=$B4,
IF('Sheet1'!$G$2:$G$738=$M4,
IF(IF(ISERROR(DATEDIF('Sheet1'!$I$2:$I$738,$F4,"d")),
DATEDIF($F4,'Sheet1'!$I$2:$I$738,"d"),
DATEDIF('Sheet1'!$I$2:$I$738,$F4,"d"))<=3,1,0)))))
Click to expand...
...

First, Excel stores dates as 60 plus the number of days from 28 Feb 1900. The
documentation claims that it's the number of days from 31 Dec 1899, but Excel
copied 123's mistake of making day 60 the nonexistent 29 Feb 1900. Anyway, to
check if two dates are within 3 days of each other, the simplest approach is

ABS(OneDate-AnotherDate)<=3

Since you're checking through an entire column of dates, you should use

=SUMPRODUCT(--(ABS('Sheet1'!$I$2:$I$738-$F4)<=3))

Next, do you want to check just that there are matches anywhere in each of the
given columns in Sheet1 for the values in a particular row in Sheet2, or do you
need to ensure that the given column in the particular row in Sheet2 all match
the corresponding columns in at least one row in Sheet1. For example, if
Sheet2!A4 matched only Sheet1!C7, Sheet2!C4 matched only Sheet1!D9, Sheet2!B4
matched only Sheet1!E11, Sheet2!M4 matched only Sheet1!G23, and Sheet2!F4 was
within 3 days of several values in Sheet1!I:I, but not the ones in rows 7, 9, 11
and 23. Would row Sheet2!4:4 satisfy the matching criteria?

If not, and if there'd have to be a single row in Sheet1 in which all given
cells in Sheet2!4:4 had corresponding matches, then you need to use something
like

=IF(SUMPRODUCT(--('Sheet1'!$C$2:$C$738=$A4),--('Sheet1'!$D$2:$D$738=$C4),
--('Sheet1'!$E$2:$E$738=$B4),--('Sheet1'!$G$2:$G$738=$M4),
--(ABS('Sheet1'!$I$2:$I$738-$F4)<=3)),"a match!","no match")
 
Thank you verry much Harlan Grove !!!

I tried Date1-Date2 but it did not work...I didnt know about the AB
function. Now it's work! ABS(Date1-Date2) was the solution...

:)

Thank you
 
Back
Top