Odd VLookup Results

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,486
Office Version
  1. 365
Platform
  1. Windows
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
DE
4417417708670000056#N/A
4417517708670000056#N/A
4417617708670000056T5_MPV_178_4WD
4417717708670000056T5_MPV_178_4WD
4417817708670000043#N/A
4417917708670000043#N/A
4418017708670000043T5_ALL_84_FWD
4418117708670000043T5_ALL_84_FWD
4418217708670000044#N/A
4418317708670000044#N/A
4418417708670000044T5_ALL_101_FWD
4418517708670000044T5_ALL_101_FWD
Sheet1
 

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,486
Office Version
  1. 365
Platform
  1. Windows
If I format as text some stay as E+13 some stay as numbers.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Eric Golf

Active Member
Joined
Dec 19, 2007
Messages
324
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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
 
Last edited:

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,486
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 25, 2013
Messages
1,336

ADVERTISEMENT

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


Regards,
DILIPandey
 

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,486
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 25, 2013
Messages
1,336

ADVERTISEMENT

Okay.. np ;)



Regards,
DILIPandey
 

Watch MrExcel Video

Forum statistics

Threads
1,129,472
Messages
5,636,515
Members
416,920
Latest member
Riskyplan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top