Using the values in an array to lookup a result in another array.

Sorry about the dodgy title, I was trying to explain it...

I've got a spreadsheet which uses data from an access database, basically what I am trying to achieve is (without individually doing separate fields) this. I have an array containing a number of assignments and their due dates.

I want to list any assignments which are due in the next 7 days.

Currently I use a Today()+1,2,3,4,5 etc in separate cells, and then match this with a matching assignments for each individual day in another array. Using a the following formula.

CDQ is the worksheet I store the array for this in obviously.

=INDEX(CDQ!$A$1:CDQ!$B$50,SMALL(IF(CDQ!$A$1:CDQ!$A$50=$C$2,ROW(CDQ!$A$1:CDQ!$B$50)),ROW(1:1)),2)

Column A in the array is the Due Date, and Column B is the Assignment name.

Essentially, what I would like to be able to do is replace the bit in purple ($C$2), with the results of a values in a single column array. (e.g. $C$2:$C$8) The idea being to display each of the assignment names in one list.

At the moment I have 7 columns, with filled-down fields, which is fine, but I'd prefer a neater approach if possible.

Any help, or rebuffage would be much appreciated.

Thanks for your reply. However, when I enter the above, there are a number of errors:

1) There are too many arguments in the ISNUMBER equation. It seems like the ',0' should be part of the IF so...

(IF(ISNUMBER(MATCH(CDQ!$A$1:CDQ!$A$50,$C$2:$C$8)),0)

2) Excel then says that it contains an error, and highlights the last 2.

I did as you suggested, and replaced it with ROWS($A$1:$A$1)

To simplify matters until this issue is solved, I decided to stick it all on the one sheet. The original array in CDQ is now E2

So with the changes above, and with the new array, this becomes

=INDEX($E$2:$F$50,SMALL(IF(ISNUMBER(MATCH($E2:$E50,$C$2:$C$8)),0), ROW($E$2:$F$50)),ROW(1:1)),ROWS($A$1:$A1))

It still gives me an error. (cursor goes just before the final close bracket)

After a bit of poking around, Excel offered the following solution:

=INDEX($E$2:$F$50,SMALL(IF(ISNUMBER(MATCH($E$2:$E$50,$C$2:$C$8)),0),ROW($E$2:$F$50)),ROW(1:1),ROW($A$1:$A$1))

Which appears to do (in theory at least) what I want it to do. In the first Cell, it returns the Due Date. In the second cell down, it returns the assignment name. Thereafter, it returns the error #REF! It appears to be just returning the first row in the array, regardless of whether it matches any of the values in $C$2:$C$8.

I'm just confused now. :P