Returning Multiple list of Values

  • Thread starter Thread starter gopalsb
  • Start date Start date
G

gopalsb

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?
 
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!
==============================================
 
Back
Top