Formula to return (where available) multiple values based on reference cell

**galvinpaddy** · 05-08-2012, 05:02 PM

Good evening Happy campers.

Attached is a sheet showing in purple the raw data, and in blue the desired result.
Now the names dont need to be in seperate cells, they can be in one cell but will need to seperated by a comma perhaps, and the layout can change if needs be.
The real list is longer and will include more names, but also have surnames involved.

So in short im looking for a formula that will return all names for a given course. (hope that makes sense)

many thanks in advance, galvinpaddy

**micope21** · 05-08-2012, 06:38 PM

Hi

Try this in array formula

If you on excel 97-2003

Cell H2 =IF(ISERROR(INDEX($B:$B,SMALL(IF($A$2:$A$21=$G2,ROW($A$2:$A$21)),COLUMNS($H$2:H2)))),"",INDEX($B:$B,SMALL(IF($A$2:$A$21=$G2,ROW($A$2:$A$21)),COLUMNS($H$2:H2)))) Control+Shift+Enter. Then copy down and cross.

If you on excel 2007-2010

use this

=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$21=$G2,ROW($A$2:$A$21)),COLUMNS($H$2:H2))),"") Control+Shift+Enter. Then copy down and cross.

**galvinpaddy** · 05-09-2012, 08:57 AM

Many thanks fella, works a treat. (rep added)
Could i be cheeky and ask you to explain why its an array and why it has to be confirmed using Control+Shift+Enter??

galvinpaddy

**micope21** · 05-09-2012, 06:22 PM

Hi

Here the link to explain you clear.http://www.cpearson.com/excel/ArrayFormulas.aspx

Please say so clearly, and mark your thread as Solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save

Good luck!