When VLOOKUP returns #N/A when there IS AN EXACT MATCH

[cgifford]

When:
-VLOOKUP fails to recognize your exact data match within the table array, and returns #N/A.

I originally called this post VLOOKUP IS BLIND!!!!
As I was writing the post asking for help I came up with the clever solution !

Here it is!


Copy your data into Notepad and then paste it back into Excel!

Enjoy,
Chase

 

[Trebor76]

Hi Chase,

Welcome to MrExcel!!

I dare say there's either a leading or trailing space (or spaces) in either your lookup_value value or the item(s) in the table_array which is resulting in the function returning a "#N/A" as indeed, an exact match with the space (or spaces) you cannot see is not being found.

These space (or spaces) must be being removed by you copying the data into Notepad and back again. Trimming the data in Excel will achieve the same.

HTH

Robert

 

[cgifford]

Perhaps the spaces were invisible because I could not delete them within Excel. I even tried doing a find / replace all to replace spaces with absolutely nothing.

I also tried setting them all to NUMBER which didn't work. Interestingly enough, some of them had different decimal places (2 vs 2.00).

It was a very strange error and I think that using Notebook to reset data in Excel may be very useful in my future for those weird bugs.

 

[Trebor76]

Different rounding would also cause the issue as would looking up a number that is a string to a number that is a value.

The 'fix' may be due to the copying of a formula to a value (via you copying it to Notepad and back).