# No Match errors with INDEX/MATCH formula

ilene_healy68056

Dear Community

I am having the hardest time debugging some #NO MATCH errors with my INDEX/MATCH formula.  I have triple checked and can’t find any reason for things not to match.  The rows right above and below work fine.  I am pulling in the percent complete into a summary view sheet.  I am matching on the site name, capability, and Stage gate (across the horizontal) Sharing with the community and hoping someone can catch my error. 

 

Here is my formula: 

INDEX({PercentComplete_LA TC1}, MATCH($[Site - Capability - Stage Gates]$45 + " " + $[Site - Capability - Stage Gates]@row + " System", {Concat_SiteCapSG_LA TC1}, 0))

Screen shot 1:  my destination summary sheet (with the broken formula) with what I am matching to in the source sheet

Screen shot 1a:  my destination summary sheet showing the same formula is work in other rows...why it won't work for a few others is a mystery..

Screen shot 2:  the detail source sheet from where I am pulling the percent complete information.  I have a helper column which is a concatenation of fields that should match up to the destination summary sheet.

Screen shot 3:  the first reference column “PercentComplete_LA TC1” that pulls the percentage complete from the source detail sheet

Screen shot 4:  the second reference column “Concat_SiteCapSG_LA TC1” which is a helper column used for the matching from the source detail sheet.  It concatenates the site (Xochimilco Figo) + Capability (Part Source Errors​) + Stage Gate (System)

 

I appreciate any help. I’m stumped!

Mike L.

If you evalute this expression =$[Site - Capability - Stage Gates]$45 + " " + $[Site - Capability - Stage Gates]@row + " System"

then paste the value in the column next to where you think the match is can you then check both values for length, spacing? 

The formula is looking for an exact match so I would try to make sure the expression is an exact match to the lookup value. Then build in the match statement. If you get match to work then add the index expression. 

ilene_healy68056

Hi Mike,

Thank you for your note.....I did copy them into excel, word and notepad to compare the match values.  They all lined up exactly......But problem solved!  I just now found the issue with the help of friend:

The value in my Smartsheet:  Past Due Orders​

I just copied and pasted that as text only to Excel and got this:  Past Due Orders?

There was some hidden character that didn't show up in all of my checking, even in notepad which I thought would have caught this issue. There isn't even a space for that character when I am in the cell in edit mode.  It fully appears that the "s" is the final character in the cell.  So I edited in the cell, clicked backspace....my cursor didn't move but that did remove the character and my formula works.

Lessons learned, use the paste as text in Excel to ensure you have no corrupted characters.

Thanks! Have a great weekend!

Ilene

 

 

Shelby

@ilene_healy68056

I'm having this same issue...I did not find any hidden characters in my index or match items. Thoughts?