# Vlookup Returning #N/A Error

#### britsgal

##### Board Regular
I'm performing a vlookup using a number where I have created a unique identifier for the number since there are several occurrences of data that I need to pull back. For example:

Starting Number: 530
Ending with: 530.001 etc.

The vlookup works fine from 530, 530.001, 530.002 but then at 530.003 the formula returns an #N/A error. We've looked at the formula and have confirmed that the values that are being looked up to are idential to the values being looked up from and that all values appear in the vlookup list. I'm perplexed as I've never seen this happen before. Any ideas?

Thanks for the help!
Laura

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Can you post your VLOOKUP formula?

could be that some values are formatted text while others are formatted General

The vlookup is as follows: =vlookup(\$a5,\$C\$5:\$D\$17,2,0). The vlookup source data is being pulled back from a different tab within the workbook. Here is an example of the data with column and row labels:

 A B C D value vlookup source data 5 530.002 409 188 930 6 530.003 #N/A 530 220 7 534 140 530.001 400 8 534.002 273 530.002 409 9 534.003 #N/A 530.003 4092 10 530.004 4900 11 530.005 4901 12 530.006 4902 13 532 220 14 534 140 15 534.001 272 16 534.002 273 17 534.003 277

<tbody>
</tbody>

The vlookup is as follows: =vlookup(\$a5,\$C\$5:\$D\$17,2,0). The vlookup source data is being pulled back from a different tab within the workbook. Here is an example of the data with column and row labels:

 A B C D value vlookup source data 5 530.002 409 188 930 6 530.003 #N/A 530 220 7 534 140 530.001 400 8 534.002 273 530.002 409 9 534.003 #N/A 530.003 4092 10 530.004 4900 11 530.005 4901 12 530.006 4902 13 532 220 14 534 140 15 534.001 272 16 534.002 273 17 534.003 277

<tbody>
</tbody>

Starting Number: 530
Ending with: 530.001 etc.

The vlookup works fine from 530, 530.001, 530.002 but then at 530.003 the formula returns an #N/A error.

When you add 0.001, do it this way (in C7, for example):

=ROUND(C6+0.001,3)

When you do simply C6+0.001, you are encountering infinitesimal arithmetic anomalies that commonly arise because Excel uses binary to represent numbers and perform arithmetic. Consequently, most non-integers cannot be represented exactly.

Using ROUND(...,3) ensures that the binary representation of the rounded expression is the same as the binary representation of the equivalent constant, 530.003 in this case.

[EDIT] Example:

 Internal Representation: Prev+0.001 Internal Representation: Constant 530.002 530.001999999999,95270627550780773162841796875 530.001999999999,95270627550780773162841796875 530.003 530.002999999999,929059413261711597442626953125 530.003000000000,0427462509833276271820068359375 534.002 534.001999999999,95270627550780773162841796875 534.001999999999,95270627550780773162841796875 534.003 534.002999999999,929059413261711597442626953125 534.003000000000,0427462509833276271820068359375

<tbody>
</tbody>

Last edited:
Worked perfectly! Thanks so much! You were a big help!

Replies
1
Views
941
Replies
10
Views
997
Replies
13
Views
700
Replies
1
Views
1K
Replies
4
Views
212

1,196,309
Messages
6,014,589
Members
441,828
Latest member
cofracr

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