return multiple corresponding values using INDEX
 

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.

return multiple corresponding values using INDEX
 

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.

return multiple corresponding values using INDEX
 

Thank you for your reply.

I'm just wondering is there a limit on setting how large the array is ? The
reason I'm asking is because when I include an array into the formula, from
$A$1:$B:3500, it only works for the first column but not another column using
similar formula but return different values. The second column only shows
"#N/A" unless I reduce the range from 1 to 500 only.

Since I do need to include that range of cells, please help on solving the
problem.

Thank you very much.

-Bubblegum



"Biff" wrote:

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.