Multiple lookup

[Guest]

I have a problem with multiple lookup. I want take your time describing the
whole spreadsheet but only describe my problem:

The user can type in dates and values in matrix A10:B14 (see below). If the
user type a date between the dates in cell A1:A2 (i.e. 01-02-2005 till
01-03-2005) I want the data typed by the user shoved in cell C11. If there
is more than one match I want the next match in cell C22 and so on.


A B
1 01-02-2005 1200
2 01-03-2005 1300
..
..

10 13-04-2005 1500
11 11-02-2005 2000
12 15-03-2005 700
14 01-01-2005 12500


Can you help me?

 

[Max]

One way ..

Use an empty col, say, col E

Put in E3:
=IF(A3="","",IF(AND(A3>=$A$1,A3<=$A$2),ROW(),""))
Copy E3 down to say, E102, to cover the max expected data in cols A & B
(Leave E1:E2 empty)

Put in C1:
=IF(ISERROR(SMALL($E:$E,ROWS($A$1:A1))),"",INDEX(A:A,MATCH(SMALL($E:$E,ROWS(
$A$1:A1)),$E:$E,0)))

Copy C1 across to D1, fill down to D100
(cover the same range size as done in col E)
Format col C as dates

Col C & D will return the desired results, neatly bunched at the top