Returning Multiple Values Based on One Value

[Guest]

I have a bunch of projects, each project has a bunch of assets under it. I
am creating a spreadsheet so that when a user clicks on a project in a list
box, it returns all of the assets/info related to the assets under that
project. I can get it to return one asset using the MATCH or INDEX function,
but I am having trouble getting it to return all the assets. Any ideas?

 

[N Harkawat]

Say your Project name is in Y1:Y10 and assets associated with that project
are in Z1:Z10
Now if the project # is in A1 and you want this A1 to be looked up then use
this array formula(Ctrl+shift+enter)
On cell B1 type this formula
=index($Z$1:$Z$10,small(if($Y$1:$Y$10=$A$1,row(A$1:a$10),row(1:1))))
and copy it down 10 rows.

It should give you all the assets within that project.

and copy it down

 

[Guest]

It tells me I have entered too few arguments for this funtion.

 

[N Harkawat]

My mistakle I inserted a wrong bracket
Now I have tested it and it works fine
This is the formula
=INDEX($Z$1:$Z$10,SMALL(IF($Y$1:$Y$10=$A$1,ROW(A$1:A$10)),ROW(1:1)))

 

[Peo Sjoblom]

Try this

=INDEX($Z$1:$Z$10,SMALL(IF($Y$1:$Y$10=$A$1,ROW(A$1:A$10)),ROW(1:1)))

note that it will not work if you insert rows above the formula

 

[Guest]

I'm not the best at getting this crap to work, so bear with me. Here is an
example file that I am working with. The button brings up a list of unique
countries. Each country can have multiple assets. When you select the
country, it returns that country to whatever cell is highlighted. I then
would like the a list of assets generated based upon what was return to that
cell.

The file is at http://www.nmberger.com/nodupes.xls