Odd VLookup Results

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,748
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
HI Dazzawm,

To take one example, are you sure that "[FONT=Arial, Arial]17708670000056" exists in column A of database sheet of workbook "Codes.xlsx" ?[/FONT]
[FONT=Arial, Arial]Is there any space in the beginning or end ?[/FONT]


[FONT=Arial, Arial]Regards,[/FONT]
[FONT=Arial, Arial]DILIPandey[/FONT]
 
Upvote 0
HI Dazzawm,

To take one example, are you sure that "17708670000056" exists in column A of database sheet of workbook "Codes.xlsx" ?
Is there any space in the beginning or end ?


Regards,
DILIPandey

Yes all seems correct and it gives the correct answer on 2 of the 4 rows.
 
Upvote 0
Okay.. can you check "17708670000056" on both places using Exact function or using equal to (=) operator ?



Regards,
DILIPandey
 
Upvote 0
I have checked them all by running both a trim and a 'death to apostrophe' macro, but still the same results.
 
Upvote 0
Hi,

Cell Formula
E44174 =VLOOKUP(D:D,[Codes.xlsx]Database!$A:$B,2,0)

The first parameter in Vlookup should be a single cell, the value you want to match. Your odd results could be to do with using the whole column.

I think the formula you want should be:-

Code:
Cell Formula 
E44174 =VLOOKUP(D44174,[Codes.xlsx]Database!$A:$B,2,0)


Hope this helps,

Eric.
 
Upvote 0
Hi,



The first parameter in Vlookup should be a single cell, the value you want to match. Your odd results could be to do with using the whole column.

I think the formula you want should be:-

Code:
Cell Formula 
E44174 =VLOOKUP(D44174,[Codes.xlsx]Database!$A:$B,2,0)


Hope this helps,

Eric.

Thanks, but still the same results.
 
Upvote 0
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
 
Upvote 0
I don't understand. E will be different as D, as it has a formula in it.
 
Upvote 0
What happens if you copy D44176 to the cell above?
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

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
Back
Top