Can I use clean() with an array reference in index-match-match?

[Erik the Awful]

I built a spreadsheet for a coworker that takes data from a website and collates it into a nice sheet that's linked into a Powerpoint that is displayed at a weekly meeting. I have the sheet set up so that is uses index-match-match to pull specific values from an array on another sheet. Unfortunately, when the website generates its excel sheet (easily importable, but looks terrible), it sometimes includes extra spaces and carriage returns in the middle of the headers, which my sheet use to find the matches. How can I get the formula to strip the spaces and carriage returns from the cells it's comparing?

Macros are not an option.

My current formula is:
=IF(ISNA(INDEX('Data'!$E$7:$M$50,MATCH($A2,'Data'!$B$7:$B$50,0),MATCH(C$1,'Data'!$E$6:$M$6,0))),"",INDEX('Data'!$E$7:$M$50,MATCH($A2,'Data'!$B$7:$B$50,0),MATCH(C$1,'Data'!$E$6:$M$6,0)))

Changing "MATCH(C$1,'Data'!$E$6:$M$6,0)" to "MATCH(CLEAN(C$1),CLEAN('Data'!$E$6:$M$6),0)" doesn't work, and I really didn't expect it to. Currently my other option is to build a separate page to wash the data, and that feels too clunky for me.

 

[Domenic]

Actually, since you want to apply CLEAN to an array of values, you'll need to confirm the formula with CONTROL+SHIFT+ENTER, not just ENTER. Also, you can use IFERROR to handle errors. Try...

=IFERROR(INDEX(Data!$E$7:$M$50,MATCH($A2,Data!$B$7:$B$50,0),MATCH(CLEAN(C$1),CLEAN(Data!$E$6:$M$6),0)),"")

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Erik the Awful]

I use index-match a lot, so I know to Ctrl+Shift+Enter, but for some reason your formula worked where mine failed. Thanks!