Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Lookup on Multiple Criteria | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Multiple lookup value's | Excel Worksheet Functions | |||
Multiple Criteria Lookup Question | Excel Discussion (Misc queries) | |||
Return Multiple Results with Lookup | Excel Worksheet Functions |