Combining fields in table into formula

Can someone assist me with writing this formula? I'd like to basically have a column in which the staff or faculty nominee column is combined with the value. For example: Cameron, Ali - Innovative Spirit

I'm running into an issue were if the staff column is not populated the staff one is, and I need the names to pull from either or both- I tried using and "if" statement but am not having any success. Additionally, if there are multiple names linked, I'd like to separate them by a comma. Screenshot attached for reference of the table!

Page 1 / 1

With reference to your screenshot, I assume you want it to display like the following, is that right?
- Cameron, Ali - Innovative Spirit, Barnsby Alisha - Innovative Spirit

If so, I think this might be a lot more complicated than one would initially assume I'm afraid, and you'll need to create some new fields to handle it:

Note that for the rollup fields you'll need to specifically use "ARRAYJOIN()" too

Here's a link to the base so you can copy the set up

I've attached the formula I ended up coming up with below, but it feels...overly complicated to say the least. Hopefully someone else has a more elegant solution!

SUBSTITUTE(
  SUBSTITUTE(
    IF(
      {Staff Nominee},
      SUBSTITUTE(
        {Commas replaced (from Staff Nominee)} & "",
        ",",
        " - " & Award & "$$"
      ),
      SUBSTITUTE(
        {Commas replaced (from Faculty Nominee)} & "",
        ",",
        " - " & Award & "$$"
      )
    ) & 
    " - " &
    Award
    , "|", ","
  ),
  "$$",", "
)

It is great seeing different solutions to the same problem.

I chose to tackle it slightly differently. This ultimately depends on how you will use the data.

I took the liberty of assuming that you have a persons table that is has a type of staff or faculty.