# Odd VLookup Results

#### Dazzawm

##### Well-known Member
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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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]

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.

Okay.. can you check "17708670000056" on both places using Exact function or using equal to (=) operator ?

Regards,
DILIPandey

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

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.

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.

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

I don't understand. E will be different as D, as it has a formula in it.

What happens if you copy D44176 to the cell above?

Replies
5
Views
85
Replies
10
Views
283
Replies
3
Views
217
Replies
3
Views
108
Replies
6
Views
83

1,203,236
Messages
6,054,298
Members
444,715
Latest member
GlitchHawk

### 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.

### Which adblocker are you using?

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

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