Populating a Text/Number field with an email address from a Contact List

Iris Riddell

Hi SmartSheet community!

I'm trying to write a formula that will allow a user to select an entry on a Contact List column, and have that person's email address auto-fill a Text/Number field (which is used in an auto-generated notification message).

What's the best way to do this? I've set up a secondary Contact List sheet to match names and email addresses, but I'm getting that pesky #INVALID_REF message. Here's what I have:

=VLOOKUP({Assigned Staff Member}, {Contact Info}, 3, true)

{Assigned Staff Member} is a Contact List column (with the contact information entered as a value), as is {Contact Info} on the separate sheet, and 3 is a Text/Number column containing the email address.

It looks like INDEX and MATCH could also be used for this, but I'm starting to go round in circles and would appreciate some guidance!

Thanks!

Genevieve P.

Hi Iris,

I personally prefer an INDEX(MATCH as it looks at the columns individually, so that's the route I'd go (but you could use a VLOOKUP instead if you prefer).

An INDEX(MATCH works like this:

=INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value to match in the other sheet}))

The [Value to Match]@row would be referencing the Contact column in your current sheet, where the formula is being placed.For example, if I'm understanding your cross sheet references correctly, you'd want something like this:

=INDEX({Text Column with Email}, MATCH([Assigned Staff Member]@row, {Contact Info}))

Let me know if this works for you! If not, it would be helpful to see screen captures of each sheet, identifying what the {references} are (but please block out any sensitive data).

Cheers,

Genevieve

Lauren Dominique

I know this is an old thread, but in case others happen to come across it... I've actually done this before, but it requires creating three helper columns.

First, create a duplicate of your contact column (let's call it "Helper-Contact"). Then switch the properties from contact to text. All of the contacts would then change to NAME <EMAIL> format.

Then, create a second helper column (we'll call it "Email") with text column properties.

Then, you'd create a third helper column (the times I've used this formula, I just call it "marker").

You'd then use the below formulas to pull over only the email from the Helper-Contact column as it's written within the <> into the Email column as text.

Formula for the marker column: =MAX(FIND("<", [Helper-Contact]@row), 2)

Formula for the Email column: =IFERROR(MID([Helper-Contact]@row, marker@row + 1, (LEN([Helper-Contact]@row) - (FIND("<", [Helper-Contact]@row))) - 1), "")