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]

If I format as text some stay as E+13 some stay as numbers.

 

[Eric Golf]

Also check for Text v Numbers e.g Does =ISTEXT(D44175) give the same result as =ISTEXT(D44176)?

When I did =Istext the 2 #N/A said False.

The response does not match my intention, you need to check a value that returns #N/A and an apparently same value that returns an answer, which is why I picked those two specific rows, as from your earlier post rows 44175 & 44176 gave different results from apparently the same lookup value (17708670000056).

I expect one is stored as text and the other is stored as a number.

Eric.

 

[Jonmo1]

I think what Eric is trying to express, is to use

=EXACT(D44175,[Codes.xlsx]Database!$A??)

?? is a the row# from column A on the Database sheet that you believe to be the exact match to D44175

 

[Dazzawm]

Thanks for everyones input but I ran a macro overnight that did the job as I had a lot of files to do this on.

 

[DILIPandey]

Okay... can you share that macro for me & everyone to understand what was the issue ?


Regards,
DILIPandey

 

[Dazzawm]

Okay... can you share that macro for me & everyone to understand what was the issue ?


Regards,
DILIPandey

The macro is very complicated and specific to my needs. It took a long time to run and there was so much data which is why I tried to do it manually using VLookup. Because that caused problems I decided to run the macro overnight which took 7 hours to complete.

 

[DILIPandey]

Okay.. np



Regards,
DILIPandey

 

[mfexcel]

Advanced vlookup – Text vs. Number | wmfexcel
take a look at this...
I believe some cells are actually "number stored as text"...

 

[mfexcel]

Excel Basic – Looks can be deceiving | wmfexcel
and also this... something basic but important to know about