Partial Match

Hello,

We have a form that populates to a Sheet 1. The employees enter general information, except for their employee number. We have Sheet 2 that has the employee# and so we are able to pull in the employee # into Sheet 1 using Match.

=IFERROR(INDEX({Master Non-BVS User ID}, MATCH(Email@row, {Master Non-BVS Email}, 0)), "")

This works perfectly as long as we just have the email address in those columns. As we start having more contacts in SS we are running into a problem were it is not recognizing the information due to the column having the full contact information (Name and Email) vs just the email address. I have been manually going to into the contact at the cell level and removing the Name for the formula to work.

After thinking about it I was thinking is a Index Match Contains formula would work and since the email would get a partial match. Looking online I found Index Collect Contains, however it isn't returning any value so it isn't recognizing the email from the contact.

=IFERROR(INDEX(COLLECT({Master Non-BVS User ID}, {Master Non-BVS Email}, CONTAINS(Email@row, @cell)), 1), "")

Thank you!

Find more posts tagged with

Accepted answers

Lucas Rayala

Hi @jpaulk I think one issue is that if you are using a Contact field, it has more than just an email in it usually. I also think there may have been a slight shift in the matrix because I used to use partial matches for contact fields--like, using CONTAINS("Saman", name@row) if I want to find instances of the name "Samantha"--but that doesn't seem to work anymore. Are these actual contact fields or just text fields with emails in them?

All comments

Lucas Rayala

jpaulk

@Lucas Rayala you were correct. I made both columns a text field and went back to the old formula. I wish that would have worked as it would eliminate some maintenance. Thank you for your assistance.

Lucas Rayala

@jpaulk -- you should put in a product request -- the contacts are really clunky to work with these days.

jpaulk

@Lucas Rayala request submitted!

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.