Return a value from the second item in a cell.


I'm trying to return an email address of the second name in a cell. I have no issues returning the email of the first name.

Name 1 (YELLOW):

=LEFT([Names]@row, FIND(",", [Names]@row) - 1)

 - Returns the 1st name in the cell

Name 1 Email (BLUE):

=IFERROR(INDEX({Email}, MATCH(LEFT([Names]@row, FIND(",", [Names]@row) - 1), {Email name}, 0)), 0)

 - Returns the email adress of the 1st name in the cell

Name 2 (ORANGE):

=RIGHT([Names]@row, (LEN([Names]@row) - FIND(", ", [Names]@row)))

- Returns the 2nd name in the cell

Name 2 Email (RED):

=IFERROR(INDEX({Email}, MATCH(RIGHT([Names]@row, (LEN([Names]@row) - FIND(", ", [Names]@row))), {Email name}, 0)), 0)

 - 0, does not return the 2nd email of the 2nd name in the cell. 

Ultimately, I would like both email addresses to be returned into one column instead of two, but I will be happy with separate columns if that isn't possible.

Does anyone have any ideas of what I'm doing wrong? Any help is appreciated!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!