Index Match Returns Blank Issue

I have an index match situation where it is returning blank and I need help troubleshooting this one. Any help would be greatly appreciated!

Here are my steps:

1) On the top sheet I am removing spaces in the text from "Name" column to "Condensed Name" column.

=SUBSTITUTE(Name@row, " ", "")

2) In Template - Deliverables Sheet, "Spot" column, I am using Join Distinct Collect to select the Name from the Asset Filename (this column is not shown in the image).

=JOIN(DISTINCT(COLLECT({Template - Primary - Condensed Name}, {Template - Primary - Condensed Name}, CONTAINS(@cell, [Asset Filename (copy here)]@row))))

3) In Template - Deliverables Sheet, "Test" column, I am using Index Match to return the WO# (no shown in image but from the Top Sheet) matching the Condensed Name (Top Sheet) and the Spot Template - Deliverables)

=INDEX({Template - Primary - Work Order}, MATCH(Spot@row, {Template - Primary - Condensed Name}, 0))

4) Test column is returning blank. I'm expected "Test" column to return WO#s.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!