Any alternative to Multiple IF statements?

Hello

I need to return a list of names to a cell if the criteria in another cell is met. Is there are better and quicker way of doing this as I need to do it for about 50 different cells!

I currently have the following formula in cell A1 which says if C25 cell has the text "Emerging" then get me the contents of B25 (which is a name) followed by a hard return. I then do loads of IF statements so eventually I have a list of names (column B) for those who has column C as Emerging. I've only written it for about 10 rows but need to check 30 rows.

=IF($C25="Emerging",+$B25&CHAR(10),"")
&IF($C26="Emerging",+$B26&CHAR(10),"")
&IF($C27="Emerging",+$B27&CHAR(10),"")
&IF($C28="Emerging",+$B28&CHAR(10),"")
&IF($C29="Emerging",+$B29&CHAR(10),"")
&IF($C30="Emerging",+$B30&CHAR(10),"")
&IF($C31="Emerging",+$B31&CHAR(10),"")
&IF($C32="Emerging",+$B32&CHAR(10),"")
&IF($C33="Emerging",+$B33&CHAR(10),"")
&IF($C34="Emerging",+$B34&CHAR(10),"")

Many thanks for any help!

How about:

=INDEX(B25:B55,MATCH("Emerging",C25:C55,0))&CHAR(10)

Obviously change the range references to suit.

BSB

Use the ConcatAll UDF from Tigeravatar, available here:

http://www.excelforum.com/tips-and-t...geravatar.html

Scroll down to get the latest version.

Hope this helps.

Pete

Thanks so much BadlySpelledBuoy. It works but only returns one match whereas I have multiple cells with "Emerging" in so need a whole list of names. Any thoughts?

Oh, I see what you're trying to do now.
For that you'll need to see Pete's response above.

BSB

Thanks Pete_UK. Had a read through of the code and looks like it should work! Thanks. Multiple answers is good for me so will use your first version.

Thanks both for your speedy help.