Totally confused - please help!

[James]

I am not sure which excel function to use.

I need to create a formula that looks for a # in a cell and returns a
corresponding person's initials. If there are multiple occurences I need to
concatenate the initials into one cell separated by a comma.

In row 1, i have the person initials
In row 2, I have the #'s
In row 3, the formula

Therefore,

A B C D E
1 PH IS CF MS AS Row 1

2 # # # Row 2

The formula result in row 3 should be PH,CF,AS

Can anyone help?

 

[Max]

In A3: =IF(A2="#",A1,"")
Copy A3 to E3

Then in F3:
=SUBSTITUTE(TRIM(A3&" "&B3&" "&C3&" "&D3&" "&E3)," ",",")
will return the required concat result

 

[James]

Max,

That is absolutely brilliant. Works like a dream. Thank you so much.

Is it a formula that will automatically update itself?

 

[Max]

Is it a formula that will automatically update itself?

All formulas update automatically, unless the calc mode is set
to/inadvertently at manual mode. Perhaps check the calc mode via clicking:
Tools > Options > Calculation tab. Ensure "Automatic" is selected > OK

Take a moment to click the "Yes" button from where you're reading this