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

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?

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 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.

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

BSB