Match Function Returning #N/A

Question

I am in process of writing some VBA code to find which column a specific header is in, so that I may further process the contents of that column.  Problem is that in many cases, where I am executing this against the Header row ( a defined range), if finds SOME of the headers and cannot find others.  

SO, I thougth I would do an experiment with a simple Match function.  Surprisingly, the results were identical to the VBA Code.  I believe if I can make the MATCH function work (finding why it is not) then my VBA code will also work.

In the image below, you can see where I have supplied the formula used by each of the cells in Column K, using the associated match formula.  Note how looking for "Decom Driver" works, but looking for Coordinator Comments does not.  This is exact same behavior I am seeing when I am searching a Range in my VBA code.  How can it POSSIBLY not be finding Coordinator Comments.  And note...i have this issue of not being able to be found on about 35% of all the column headers.  Others work perfectly.

I have run a CLEAN function and re-pasted the cleaned values into the headers, but no difference, thinking that there may be a spurious character not visible, in the header.  But problem persists.  Ideas?  Seems like some sort of "bug" but I won't assume that, for now.

Thx!

PK

Answer 1

When a MATCH() failed to find the Lookup_Value (What you are looking for) in Lookup_Array (Data Range) then it returns #N/A Error.

So, I'm 100% sure that your Lookup_Value is not present EXACTLY in lookup data range.

Answer 2

Truly...I have to agree with you all...been doing this for a long time.  But here's something interesting...i emailed the file to myself, at home, and opened the file in excel on my personal system.  WORKS PERFECTLY as expected.  Go figure.  So something "odd" with my work PC version of excel (I think).

Work PC is office 365, and my home PC is my own personal Office 2016 version of excel.

This is crazy.

Maybe I need to re-install on work PC

Thanks all

Pk

Answer 3

Hi,

Share the link from where I can download your workbook.

Answer 4

Yes... We have long since eliminated spelling, etc. We even had a second person create a, stand-alone new workbook, telling him what headings to use (no copy paste). Identical results. 

And as I noted in post, we had already used the Clean function as well, to ensure no spurious character. 

So while I would have to agree with you in principle, the actual results are stating otherwise.

Answer 5

Hi,

This problem 100% arises only because of spelling differences/extra spaces.  Recheck.