Index Match Returns Blank Issue

bFer

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.

Paul Newcome

Can you double check the formulas you provided? The JOIN formula should be throwing an error because you have not specified a delimiter.

bFer

Thank you, this is the JOIN formula that is currently working:

=JOIN(DISTINCT(COLLECT({Roseville - Reference Sheet Range 2}, {Roseville - Reference Sheet Range 2}, CONTAINS(@cell, [Asset Filename (copy here)]@row)), " "))

Paul Newcome

Try changing the delimiter from a space to a blank.

" "

to

""

bFer

That still works, thank you, but when I reference that cell in this formula in WO# column, it still returns blank:

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

Paul Newcome

Double check your source data. Look for the first row that has Spot1 in it then make sure the WO column is not blank. If it is not blank, then double check your range is set up properly.

bFer

okay thanks, let me check this and I'll get back.

bFer

I checked, still getting no value in WO# Column. I think the issue lies in the JOIN DISTINCT COLLECT formula, the index match is not seeing a match. When I make the "Spot" formula invalid the "WO#" formula sees it.

Column "Spot"

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

Column "WO#"

=INDEX({Template - PRIMARY Range 1}, MATCH(Spot@row, {Template - Primary - Name}, 0))

Paul Newcome

Can you provide screenshots of the ranges in your INDEX/MATCH along with the very top row that matches the Spot1?

What happens when you manually type Spot1?

bFer

Sure thing, here you go

this is the formula currently in WO#:

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

When I manually type in SpotName2 into "Spot" column i get blank cell in WO#, same.

Paul Newcome

And what exactly is in the {Template - Primary - Work Order} column for the very top row where "SpotName2" is in the source sheet?

bFer

Okay, I see, thank you! You were right, that cell was blank which was giving me blank for all. I added the WO#s and it works. I greatly appreciate your time and help with this.

Paul Newcome

Happy to help. 👍️