# No Match errors with INDEX/MATCH formula

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!

screen shot 1a.png

Screen shot 2.png

Screen shot 1.png

Screen shot 3.png

Screen shot 4.png

Find more posts tagged with

Formulas

Comments

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?

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.