Same formula but isnt working

[bojan0810]

So I have this formula.

=INDEX(All!$A$7:$F$10,MATCH(1,(All!$A7:$A10=L2)*(All!$E7:$E10=MAX(IF(All!$E7:$E10<=H1,All!$E7:$E10))),0),6)

and its working fine like this, but if I change it to bigger list

=INDEX(All!$A$7:$F$100,MATCH(1,(All!$A7:$A100=L2)*(All!$E7:$E100=MAX(IF(All!$E7:$E100<=H1,All!$E7:$E100))),0),6)

then it isnt working at all. Can someone explaine me why? It is really annoying I must say.

list is full, every row has same format, etc.

Basically, it is pulling out data based on one condition and other condition is date that must be greater then date in list. Greater then "max" date...

 

[Claus Busch]

Hi Bojan,

Am Mon, 6 Oct 2014 09:38:36 -0700 (PDT) schrieb (e-mail address removed):

=INDEX(All!$A$7:$F$100,MATCH(1,(All!$A7:$A100=L2)*(All!$E7:$E100=MAX(IF(All!$E7:$E100<=H1,All!$E7:$E100))),0),6)

did you insert the formula with CTRL+Shift+Enter?


Regards
Claus B.

 

[bojan0810]

Dana ponedjeljak, 6. listopada 2014. 19:20:37 UTC+2, korisnik Claus Busch napisao je:

Hi Bojan,



Am Mon, 6 Oct 2014 09:38:36 -0700 (PDT) schrieb (e-mail address removed):






did you insert the formula with CTRL+Shift+Enter?





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional

Yeah I did. Both of them

 

[Claus Busch]

Hi Bojan,

Am Mon, 6 Oct 2014 11:48:31 -0700 (PDT) schrieb (e-mail address removed):

Yeah I did. Both of them

I tested it and it worked fine. You can also try:
=INDEX(All!$F$7:$F$100,MATCH(1,(All!$A7:$A100=L2)*(All!$E7:$E100=MAX(IF(All!$E7:$E100<=H1,All!$E7:$E100))),0))
and insert the formula with CTRL+Shift+Enter


Regards
Claus B.

 

[bojan0810]

Dana ponedjeljak, 6. listopada 2014. 21:16:06 UTC+2, korisnik Claus Busch napisao je:

Hi Bojan,



Am Mon, 6 Oct 2014 11:48:31 -0700 (PDT) schrieb (e-mail address removed):






I tested it and it worked fine. You can also try:

=INDEX(All!$F$7:$F$100,MATCH(1,(All!$A7:$A100=L2)*(All!$E7:$E100=MAX(IF(All!$E7:$E100<=H1,All!$E7:$E100))),0))

and insert the formula with CTRL+Shift+Enter





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional

Here is still same. I am not sure why it isnt working. Is there any other formula that will "lookup" for in this case L2 and that date (h1) greater then one from the list. For example 10/1/2014 is H1. And for L2 there are multiply values. So I need value what is L2 greater then closest date.