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!
Best Answer
-
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?
Answers
-
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?
-
@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.
-
@jpaulk -- you should put in a product request -- the contacts are really clunky to work with these days.
-
@Lucas Rayala request submitted!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!