MultiLookup with Multiple Criterions with Multiple values falling in Date Ranges.

MultiLookup with Multiple Criterions with Multiple values falling in Date Ranges.

Dear Forum,

Please do not see the query as a repeat of my previous threads as its a completely new requirement..
Its just that MUltilOOkup is a wide and neccessary requirement..

P.S. I always try to name the Threads based on the concepts so thats it may help others to even refer them for their needs..

About the requirement now,
I have data of Banking Accounts which are closed in one of the years and they need to be extracted only if they appear in a
Financial Year Range such as 1st-Apr-XXXX to 31st-Mar-XXXX+1

Please see the attachement for more information..

Regards
E

Again, assuming I've understood the requirements and given the existing setup

First - add the conditional count to restrict no. of subsequent Arrays

Please Login or Register  to view this content.

Originally Posted by e4excel

It works well but it displays only two values for the Financial Period of
1st Apr 2009 to 31st Mar 2010
End Date of Financial Year----31-Mar-2010

It should display 4 values for 2010 as the Closing Date are falling in the range of the above financial period..

Then why categorically state otherwise in your sample file ?

If you want those closed mid year to be active in that years return change the Date tests accordingly, ie: >= rather than >

The formula you have does what you asked for per the sample and the suggested revision (ie simply modifying operator test from > to >=) does what you later requested.
Given the above there's nothing further I can add - I suggest you review my suggestions and implement accordingly.

Incidentally - your examples above are wrong - both dates fall into FY10 - ie the +1 does not exist (it is 0 in both cases)

Originally Posted by e4excel

"Simply put any date after the 31st Mar of any year ( XXXX ) would appear in the next Financial Year of 1st Apr XXXX to 31st Mar XXXX+1"

So, the dates
24-Feb-10 ------1st Apr XXXX to 31st Mar XXXX+1 where X= year(24-Feb-10)
31-Jan-10 ------1st Apr XXXX to 31st Mar XXXX+1


Regards
E

You are right, I [Mod: edit - awkward translation!] made a mistake in explaining it should be where X= year(24-Feb-10)-1
So the answer would be 2010-1=2009..

SO, 1st Apr XXXX to 31st Mar XXXX+1 -->1st Apr 2009 to 31st Mar 2010

Dear DO, I am really sorry for the above mistake but I will explain the need, this bank account nos should appear only if ther out of the the Range..

The probable answers are as shown..

31-Mar-2010--------------31-Mar-2011

6049 414 8837-----------6048 414 8837
6049 414 8728-----------6048 464 8846
6049 464 3847
6049 464 8846

when the criterions selected are
Akhilesh Chodhury

Bank C

1st Apr 2009 to 31st Mar 2010

31-Mar-2010

I tried doing that ">" to ">=" but I am afraid it did not work, I would appreciate if you have a look at it just one more time please..

Regards

SOrry for the confusion.!

I got it working as i said and I posted the reply maybe simultaneously unaware of your reponse..

I was just trying to foucs more on the code in the Cell D26 and forgot to make the changes in the cell D25 and then after lot of trial and error did it unbeknownst of your replies..

So all in all, i am sorry for any inconvenience caused to you and thanks a lot for the help.
Thread already marked as SOLVED..

REgards E