Looking at your dummy file, the long winded method you've tried is about the only non-vba way of completing the task

I've been trying to put together what should be a simple udf to split the references in column P, perform a lookup on each string, then concatenate the results.

At the moment it's returning errors not results, I'm not going to be defeated, but don't think I'm going to get a working version posted today.