INDEX/MATCH returning #NO MATCH error but only for ONE cell

ro.fei

I have the following formula checking for matching emails in two sheets & returning the date:

=IF(Done@row = 1, INDEX({Submission Date}, MATCH([Email Address]@row, {Submission Email})), "")

It's inside an IF statement because the Done column checks if there's a submission for the email in the first place--I only want the date if there has been a submission by that person. Just in case the issue is somehow related to that, here's that formula too:

=IF(CONTAINS([Email Address]@row, {Submission Email}), 1, 0)

It's working perfectly for dozens of cells, but for some reason this one email address is returning the #NO MATCH error & I have no idea why. I've tried copy/pasting the same exact string from one sheet to the other. I've tried deleting entirely & retyping. I'm still getting the #NO MATCH error every single time, but only for this one person & I have no clue how to fix it--the formula is working great for everyone else!!

For obvious reasons, I can't share the exact email address here. But I will point out that there is absolutely nothing special about it. It's set up the same as everyone else's: firstname.lastname@company.com. No special characters, no different format, just a standard issue email address set up the same as everyone else's. Also yes, the Submission Date has a date in it. All columns are fully filled out.

This is driving me absolutely insane because there doesn't seem to be any logic in why this particular email is giving me an error. Please help so I can return to sanity.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/365222#Comment_365222

Weird! Ok, next thought would be that the INDEX/MATCH is missing something:

=IF(Done@row = 1, INDEX({Submission Date}, MATCH([Email Address]@row, {Submission Email}, 0)), "")

Add this , 0 (comma, space, zero) after the {Submission Email} range.

Jeff Reisman

@ro.fei I am suspecting that there's a leading or trailing <space> in the email address. Here's why I think that:

Your Done column formula is using CONTAINS to match the email address with the submission email. CONTAINS can match either an entire cell or a sub-string within a cell. So if I use CONTAINS with "SmartsheetGuru@test.com" and the field I'm searching has "TheSmartsheetGuru@test.com", it's going to consider that a match, because it found the string value contained within the cell.

Meanwhile, your index/match is looking for an exact match with the entire cell value. So "SmartsheetGuru@test.com" will not match "TheSmartsheetGuru@test.com" or "SmartsheetGuru@test.com " (empty space after the dot com.)

ro.fei

@Jeff Reisman I wish you were right, but that was my thought as well. There are no spaces in either entry. I didn't even trust that after checking, so I deleted both cells on both sheets & retyped them myself. Still getting #NO MATCH.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/365222#Comment_365222

Weird! Ok, next thought would be that the INDEX/MATCH is missing something:

=IF(Done@row = 1, INDEX({Submission Date}, MATCH([Email Address]@row, {Submission Email}, 0)), "")

Add this , 0 (comma, space, zero) after the {Submission Email} range.

ro.fei

@Jeff Reisman That worked!!! Thank you so much, this has been driving me insane!