how can i match e-mails that are the same in different sheets but have different names, for example

sheet 1, e-mail stated John Do, John.do@smartsheet.com

sheet 2, e-mail states Do John, john.do@smartsheet.com

Even though the e-mail address is the same I can not find them using vlookup, I get a No match error, this is caused by the fact that the names do not match. I don't understand why the name is taken in consideration for the match, I'm looking for the e-mail address not the name.

Does anyone have an idear how to solve this. I have got a lot of sheets with reports that are based on e-mail data and as such my results are not reflecting the reality because the e-mails don't match.

Thanks for your idears.

Find more posts tagged with

Accepted answers

All comments

Amanda Alv

Hi there,

I believe we will need to use the HAS command to help, but to clarify, what are you looking to have done once they are matched? On Sheet A, are you trying to check a box or display a specific response if the email listed is also listed on Sheet B?

Stephanie B.

I have this same problem. I need to compare the email address is one sheet with the email address in another sheet to confirm they are the same. One is submitted by form (validation set to email) that feeds a sheet with Admins, and another in a tracker we have set up separately. If they aren't, I'll create an automation to let the sheet admins know, so they can correct the email address.

I'm not sure whether this is relevant but the email address will appear multiple times in the sheet with form submissions.

I've tried this as both text/number and as contact list column types, but neither works.

I've also tried a few different options with IF formulas including vlookup, index/match, contains, and has. They return a variety of errors including #unparseable, #invalidref, #invalid data type, #invalid operation and #no match.. I've also gotten it to work (where I am now), but the answer isn't correct.

The formula I'm using at the moment is below. It returns a #No Match error. I'm using the email addresses as text/number.

=IF(INDEX({spark! Text Email Address}, MATCH([Text Email Address]@row, {spark! Text Email Address}, 0)), "Yes", "No")

If I change from text email address to Participant Email Address (contact) and make no other changes, I get #INVALID DATA TYPE.

I'm at my wit's end. Can someone please share what the correct formula should be?

Thank you!

Steph

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.