Odd VLookup Results

[Dazzawm]

I am using VLookup but getting some strange results. Although the data matches some return #N/A. Please look below for examples. Why is this please?

Excel Workbook
	D	E
44174	17708670000056	#N/A
44175	17708670000056	#N/A
44176	17708670000056	T5_MPV_178_4WD
44177	17708670000056	T5_MPV_178_4WD
44178	17708670000043	#N/A
44179	17708670000043	#N/A
44180	17708670000043	T5_ALL_84_FWD
44181	17708670000043	T5_ALL_84_FWD
44182	17708670000044	#N/A
44183	17708670000044	#N/A
44184	17708670000044	T5_ALL_101_FWD
44185	17708670000044	T5_ALL_101_FWD
Sheet1

 

[Dazzawm]

What happens if you copy D44176 to the cell above?

Still has #N/A.

 

[DILIPandey]

Hi Dazzawm,


Can you upload that workbook on dropbox and provide the public link ?


Regards,
DILIPandey

 

[Dazzawm]

Hi Dazzawm,


Can you upload that workbook on dropbox and provide the public link ?


Regards,
DILIPandey

Sorry it is too big and has a lot of private data.

 

[DILIPandey]

Okay.. can you just copy "17708670000056" from both the places and then upload using excel file on dropbox ?



Regards,
DILIPandey

 

[RoryA]

Are you still using the VLOOKUP(D:D... version or the single cell version?

 

[Dazzawm]

I have tried both.

 

[Eric Golf]

Hi,

Then D44175 must be different from E44175. Did you try seeing what =EXACT(D44175,E44175), as previously suggested, gives you? Also check for Text v Numbers e.g Does =ISTEXT(D44175) give the same result as =ISTEXT(44176)?

Eric

Sorry, my bad, got myself confused.

meant:-

Then D44175 must be different from D44176. Did you try seeing what =EXACT(D44175,D44176), as previously suggested, gives you? Also check for Text v Numbers e.g Does =ISTEXT(D44175) give the same result as =ISTEXT(D44176)?

i.e If the same formula gives different results then the input must be different, despite appearance D44175 must be different from D44176.

Again, sorry for the confusion,

Eric

 

[Dazzawm]

Right, when I did =Exact 1 of the #N/A said true the other false. When I did =Istext the 2 #N/A said False.

 

[Dazzawm]

If I highlight column D with long numbers and format as text all the #N/A change to E+13 at the end. If I format them all as a number with zero decimal places they appear as they should. I select format as text again and they are back to E+13?!

 

[RoryA]

Sounds like you have a mix of text and number data in there. Format as text, then copy and paste special values.