dynamic range in lookup function

[paulpap]

In a worksheet I have the following data:

column A:dates from eg 1/1/2011 -> 31/12/2012 (number of rows dynamicaly expanded)

column B:numbers

the task is to select a random month of a year (eg.march 2012) and to present the values of column b with a lookup function without scanning entire column A but only the rows containing march 2012.

So far I have in 2 variables the minimum row number concerning the 1/3/2012 and the maximum row number concerning 31/3/2012.(eg mn=426,mx=456).

On cell D1 I have the value 1/3/2012 and on cell e1=LOOKUP(D1;A1:A2000;B1:B2000)

What can I do to replace the "A1:A2000"&"B1:B2000" with value in column B,row 426->456 ?


Thanks in advance

 

[Luke M]

Starting with the technique I explain here:

http://chandoo.org/wp/2011/11/18/formula-forensics-003/


Your array formula would look something like:

=IF(SUMPRODUCT(1*(TEXT($A$2:$A$10,"mmyyyy")=TEXT($D$1,"mmyyyy"))) < ROWS($E$2:E2), "", INDEX(B:B, SMALL( IF(TEXT($A$2:$A$10000,"mmyyyy") =TEXT($D$1,"mmyyyy"), ROW( $A$2:$A$10000)), ROW(A1))))


Now you don't need to know the Min/Max rows. Formula will find all matching month/year cells and return corresponding value. After confirming formula in E1 via Ctrl+Shift+Enter, copy down as far as you think would ever be needed.