Returning Multiple list of Values

[[email protected]]

Hi,

I am new to the use of Index function.I was trying to use this for
solving a particular problem-

I have sheet1 one with 2 columns,A & E, full of string values.Sheet2
has a column where values are restricted by lists.When the user selects
a value , I should be able to search by this value in sheet1 in Column
A and get a list of values from column E of sheet1.These columns have a
value in Column A which equals the search value.

for e.g.

Sheet 1

Col A ColE

AAA Pack1
AAA Pack2
AAX Pack3
AAE Pack4


Sheet2 :User Selects a value of AAA and one column in sheet2 is
currently populate with this formula

=(INDEX($A$1:$E$65535,MATCH(Sheet2!$C1,$A$1:$A$65535,0),5))

The above formula retuns only one value. I am expecting an array of
values, 2 to be exact .How is this possible using Index or is there any
other funtion which can do the above job.What am i doing wrong in the
baove code?

[RagDyer]

The formula you're using will only return the *first* match.

To return an array of matches, try this *array* formula:

=INDEX($E$1:$E$300,SMALL(IF($A$1:$A$300=Sheet2!$C$1,ROW($1:$300)),ROW(A1)))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

Adjust your ranges and *don't* use all the available rows as you did in the
formula you posted.
This is an *array* formula, which uses resources, and you shouldn't use more
than what you need.

The rows in the Row($1:$300), is the actual *number* of rows in your list,
and does *not* pertain to addresses.

Copy down as many rows as you anticipate a return.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


<[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I am new to the use of Index function.I was trying to use this for
> solving a particular problem-
>
> I have sheet1 one with 2 columns,A & E, full of string values.Sheet2
> has a column where values are restricted by lists.When the user selects
> a value , I should be able to search by this value in sheet1 in Column
> A and get a list of values from column E of sheet1.These columns have a
> value in Column A which equals the search value.
>
> for e.g.
>
> Sheet 1
>
> Col A ColE
>
> AAA Pack1
> AAA Pack2
> AAX Pack3
> AAE Pack4
>
>
> Sheet2 :User Selects a value of AAA and one column in sheet2 is
> currently populate with this formula
>
> =(INDEX($A$1:$E$65535,MATCH(Sheet2!$C1,$A$1:$A$65535,0),5))
>
> The above formula retuns only one value. I am expecting an array of
> values, 2 to be exact .How is this possible using Index or is there any
> other funtion which can do the above job.What am i doing wrong in the
> baove code?
>

[[email protected]]

Hi RD,

Thanks for the information.

Regards
Gopal