INDEX MATCH is returning #NO MATCH or the wrong email address

Spencer H

I have a Smartsheet that I have populated with INDEX MATCH, and it works great. My last column is to pull in the email addresses of all the employees, based on their employee ID that has previously loaded into the Smartsheet via an INDEX MATCH. So my [Site Contact 1 ID\@row is data sourced from an INDEX MATCH already. I am wondering if that is why I am having issues.

Smartsheet 1 has Location assignment# and employee ID#

Smartsheet 2 has the employee ID# and employee email address

Smartsheet X is populated by INDEX MATCH with location assignment # and employee ID#. Now when I want to INDEX MATCH for the email address on SS2 and reference the employee ID in SSX, it will not work. it will assign the wrong email addresses or give a #NO MATCH

=INDEX({Contacts - Account Team - 1-13-23 Range 1}, MATCH([Site Contact 1 ID]@row, {Contacts - Account Team - 1-13-23 Range 2}), 0)

thoughts?

Paul Newcome

The data to match on being brought in by an INDEX/MATCH should not cause an issue. Are you able to provide some screenshots for reference with sensitive data blocked out as well as the formula that is being used to bring in the ID?

Jeff Reisman

@Spencer H What is the format of your employee ID#? Is it all numeric, or mixed (alphanumeric?) If it's all numeric, are you sure that these values are stored as numbers everywhere? Because numbers stored as numbers won't match numbers stored as text.

What happens if you copy the employee ID# values in SSX into a helper column, and try your index/match using that helper column for the employee ID# value?

Spencer H

So I tried the helper column, that did not work. I also went back and uploaded SS2 again and before I did i made sure the employee IDs were formatted to numbers. They are numbers only.

Here you can see the ID i pulled in and they are correct. But the email addresses are not. The only match I have is for Calevro. All his and two other employees pulled in right, but the rest is wrong or has a #NO MATCH. I have 51 employees and about 3200 sites, so it is a large sheet.

Paul Newcome

What is in the reference sheet for ID 1044155?

Spencer H

The reference sheet for all IDs is the same. That's why this is confusing to me. Why would it pull correctly for some but not for others.

Jeff Reisman

@Spencer H

Ok, another thing to try is helper columns for your ID numbers. Make them Text/Number, and use the VALUE function on the values from the other ID columns:

=VALUE([Site Contact 1 ID]@row)

Then, on the rows that are getting no match, try to index/match using those helpers for the ID columns.

Paul Newcome

I just noticed a syntax issue. The zero should be INSIDE of the MATCH function.

=INDEX(............, MATCH(..................., 0))

You have

=INDEX(............, MATCH(...................), 0)