Hi!

=INDEX(Sheet2!$A:$B, SMALL(IF(Sheet2!$B$2:$B$6=Sheet1!$B$1,
ROW(Sheet2!$B$2:$B$6)), ROW(1:1)),1)

Since you're only interested in returning data from a single column it's not
necessary to index more than that single column:

=INDEX(Sheet2!$A:$B

The problem you have is this:

ROW(Sheet2!$B$2:$B$6)

That has to be the EXACT same size as the range that you have indexed:
INDEX(Sheet2!$A:$B

Note that ROW(Sheet2!A:B) is not a valid range and would error.

Use the actual range and not just the whole column (unless you really are
using the ENTIRE column).

Try this as a guide:

=INDEX(Sheet2!A$2:A$10,SMALL(IF(Sheet2!B$2:B$10=B$ 1,ROW(B$2:B$10)-ROW(B$2)+1),ROWS($1:1)))

I'm assuming you know that's an array formula.

Biff

"BubbleGum" wrote in message
...
Hello,

Please help me on getting the correct way to lookup a value and return
multiple corresponding values.

In sheet1 first row, I list out the dates as: 11 01 05, 11 02 05, 11 03 05
etc...
In sheet2 Column A, it is a list of account number; where as Column B is
a
list of dates.

I would like to use the dates as appear in sheet1 and return the
corresponding account number. Since there are multiple account number
which
match with the dates, so, vlookup cannot be used. I tried the following
but
it doesn't work:

=INDEX(Sheet2!$A:$B, SMALL(IF(Sheet2!$B$2:$B$6=Sheet1!$B$1,
ROW(Sheet2!$B$2:$B$6)), ROW(1:1)),1)

Your reply is much appreciated.