Same formula but isnt working

  • Thread starter Thread starter bojan0810
  • Start date Start date
B

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...
 
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)
Click to expand...

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


Regards
Claus B.
 
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
Click to expand...

Yeah I did. Both of them
 
Hi Bojan,

Am Mon, 6 Oct 2014 11:48:31 -0700 (PDT) schrieb (e-mail address removed):
Yeah I did. Both of them
Click to expand...

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.
 
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
Click to expand...

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.
 
Back
Top