Array Commands to solve this

**eltonlaw** · 05-01-2013, 02:36 AM

Dear Expert,

I am not familiar wih Function Array. But I think only array can solve this.

I have put into Excel file.

Column E is my dersired outcome.

If either exception 1 or exception 2 contains "x", shows blank

If both exception 1 & exception 2 contains "x" and then have to check if ID appears the first time

If ID appears the first time, set expiry date to be Date + 90 days

If ID appears not the first time, check if Trade date is greater than the first time expiry date for that ID

If it's greater than first time expiry, default remarks "passed expiry date"

If Trade date is smaller than the first time expiry date, refer to the first time expiry date for that ID

I hope expert can help me as a lot of conditions involved.

Thanks
Elton

**eltonlaw** · 05-01-2013, 04:59 AM

=IF(AND(C2="x",D2="x"),IF(B2>IF(COUNTIFS($A$2:A2,A2,$C$2:C2,"x",$D$2:$D2,"x")>1,VLOOKUP(A2,$A$2:$E$15,5,0),EDATE(B2,3)),"Passed expiry date",IF(COUNTIFS($A$2:A2,A2,$C$2:C2,"x",$D$2:$D2,"x")>1,VLOOKUP(A2,$A$2:$E$15,5,0),EDATE(B2,3))),"")

Someone helped me already. Thanks

**Marcol** · 05-01-2013, 05:01 AM

Try this in E2 Drag/Fill down

Formula:

Please Login or Register  to view this content.

Note that your request is for the first date + 90 days, but your sample sheet indicates 92 days.
Change the 90 in the formula as required.

[EDIT]
I hope the "Someone" wasn't on a different forum, cross posting without warning isn't nice.

For the record I missed a condition and it is now corrected.