"#NO MATCH" Error for Vlookup with duplicate contacts

Luke Serrato

I have a problem where I have a very simple "VLOOKUP" formula meant to return a contact from another sheet.

Of the 9 contacts in the field, only 3 are returning an error that stats "#NO MATCH". All of the other contacts are returning and displaying correctly.

The only connection I can find is that all three of the contacts that are failing to populate correctly have a duplicate email address, I.E. "John Smith john.smith@email.com" AND "Smith John john.smith@email.com".

All three contacts have identical email addresses, and are only transposed in the "First" & "Last" name fields organized by smartsheet.

Since I have a business account and not an enterprise, I was unable to access any contact merger options, and am unable to list an alias email address, since the emails are the same.

I did notice that the "Last First" contact is located in My Contacts, while the "First Last" contact is located in a business/departmental address book.

My attempts to delete the contact from my address book, reselect the contact from the column drop down options, delete and re-add the contact to my address book, etc, have all failed to work.

I've even gone as far as to painstakingly recreate my entire spreadsheet after deleting the duplicate contacts from "My Contacts", however the formula still trips the same "#NO MATCH" error code.

If anyone can help me with this I would greatly appreciated. Customer Success Manager, Angie Anderson, was extremely helpful on my phone call earlier but was unable to see a readily available solution to this problem.

If this issue needs to be resolved by merging the contacts, then it needs to be a functionality offered to business accounts. Unless of course SmartSheet wants to handcuff themselves to being involved in the solution process by being bugged by the client until the problem can be resolved.

At present, I am unable to continue developing this project for my 2500 person department until this issue is resolved.

Thank you in advance for your time and any help you can offer.

-Luke

Mark Cronk

Hi @Luke Serrato ,

I'm not convinced this is a contact list issue. You're looking up text against text and not getting a match. Or, is your [Text Name] column actually populated with a formula? Is your [(H) Hierarchy Count] column populated hy a formula? If so, what formula are you using and can you share screenshots of those columns and the formula?

You may have already done it, but I'd start by adding FALSE after the 2, in your vlookup to force an exact match. See if that makes any difference.

Thanks for challenging us. This is a great problem for the Community to solve.

Mark