Return a value from the second item in a cell.

CNaenfeldt

Hello,

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!

gstotts

Your IFERROR is set to return 0 if it does not find a user -- does it return "NO MATCH" if you remove the IFERROR? A "NO MATCH" error would indicate there is no John Smith in the referenced lookup you are using for the names of emails.

CNaenfeldt

@gstotts Yes, then I get #NO MATCH, but I clearly have the name I'm referencing in the other sheet, so I'm a little confused.

CNaenfeldt

I figured it out:

Before:

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

Now:

=INDEX({Email}, MATCH(RIGHT(Names@row, (LEN(Names@row) - FIND(", ", Names@row) - 1)), {Email Names}, 0))

There must be a character that I wasn't seeing or something.

gstotts

Glad you got it!

Paul Newcome

For future reference: It was the space. Smartsheet won't display a space at the beginning of a string which is why "John Smith" looks fine, but it does store it on the back-end which is why you were getting a no match error. You were actually pulling " John Smith".