return multiple corresponding values using INDEX

[BubbleGum]

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.

[Biff]

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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

[BubbleGum]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
>
>
>