Multiple lookup
 

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 C1:D1. If there
is more than one match I want the next match in cell C2:D2 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?

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"VALIUM" wrote in message
...
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 C1:D1. If
there
is more than one match I want the next match in cell C2:D2 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?