This makes ABSOLUTELY NO SENSE! Why can't excel fine values that are plainly RIGHT THERE! Check my logic please and help troubleshoot.

[logandiana]

Last week a posted a message to the board.

VBA Application.Match keeps throwing 2042 error

I have to fill columns of a template using column heading names, because sometimes the template shifts and the columns move. I have to find which column is correct before adding the data under the header. I am using the following code, and I keep getting ERROR 2042, but I am only getting the...

www.mrexcel.com

In which I was trying to use application.match to use header string to identify which columns needed to be used.
I tried several different variations of code and got similar results, some values just wouldn't be found while others would.

I thought I'd just approach this from an easier approach to see if I could identify what is going on and then go from there.

In cell A1 I have the text "Logical Partner"
In cell B1 I have the text "Sequence"

Now for a simple formula in cell A3. =match(A1,A1:B1,0) The result I get is 1, okay good that's what I was expecting.
Now, same formula only this time replace A1 in the formula with actual text, =match("Logical Partner",A1:B1,0) , should still be 1 right? I get #N/A!!!!

Let's try simpler. In A2 I type "Logical Partner" and the formula I enter now is =A1=A2 The result TRUE, okay good that's what I was expecting again
Now I change the formula to =A1="Logical Partner" aaaaaannndddd… FALSE!

Just like the link above from the other day, it doesn't happen in every column. For instance in cell F1 I have "Division", and everything works as expected.

I can't figure this one out. I have tried starting over thinking my workbook was corrupt. I've tried trim() and clean() thinking I was missing some missing characters. All the results are still the same.
Is there some bank of word strings that you are not supposed to lookup?

 

[mikerickson]

Is there a trailing space after "Partner"?
Is the space a true space or perhaps CHR(160)

 

[joeu2004]

I've tried trim() and clean() thinking I was missing some missing characters.

In addition, try SUBSTITUTE(A1,CHAR(160),""). Putting it all together:

=CLEAN(TRIM(SUBSTITUTE(A1,CHAR(160),"")))

In A2 I type "Logical Partner" and the formula I enter now is =A1=A2 The result TRUE, okay good that's what I was expecting again[.] Now I change the formula to =A1="Logical Partner" aaaaaannndddd… FALSE!

That does seem strange, if the facts are correct. I can only guess that you might have typed "Logical Partner" differently in the three examples (A1, A2 and the formula). I fat-finger things all the time!

But if the CLEAN/TRIM/SUBSTITUTE work-around does not remedy all problems, I suggest that you upload an example Excel file (redacted) to a file-sharing website, and post the download URL in a response here. The devil is in the details that we will not be to see otherwise.

 

[mikerickson]

Another possibility is a terminal TAB or CHAR(13) or CHAR(10) character

 

[AlphaFrog]

To be clear, are you typing in the cell "Logical Partner" excluding the quotes?

 

[Peter_SSs]

I'm suspecting, as others have already suggested, that not all your characters that look like a space are identical. Apart from that being a reasonable conclusion from the information given, I note that in that other thread, the first value that worked correctly was "Division" and that is the first value in the list that did not contain any type of 'space character'

BTW, since somebody has tried to help you in that other thread, it would be good if you gave them some feedback.
Refer to points 6 and 8 of the Guidelines

 

[logandiana]

Thank you to all that responded. It was CHAR(160), and I have taken the steps to remove it. I do have a follow up question though, what is CHAR(160)?, when and why is used instead of " " (space)?, and why didn't the CLEAN formula remove it? I thought CLEAN was to remove any non-letter/numbers characters...

Thanks again

 

[MARK858]

It's a non-breaking space which is commonly used on the web, Clean was designed to remove the first 32 non-printing characters.

See this link about Clean