Partial Match

jpaulk ✭✭
edited 03/06/24 in Formulas and Functions


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

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    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?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!