Vlookup Returning #N/A Error

[britsgal]

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
Adding to starting number: .001
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

 

[Brian from Maui]

Can you post your VLOOKUP formula?

 

[gorem]

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

 

[britsgal]

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:


[TABLE="width: 240"]
<tbody>[TR]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl76, width: 64, bgcolor: transparent"]A
[/TD]
[TD="class: xl76, width: 64, bgcolor: transparent"]B
[/TD]
[TD="class: xl76, width: 64, bgcolor: transparent"]C
[/TD]
[TD="class: xl76, width: 64, bgcolor: transparent"]D
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]value
[/TD]
[TD="class: xl66, bgcolor: transparent"]vlookup
[/TD]
[TD="class: xl65, bgcolor: transparent, colspan: 2"]source data
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]5
[/TD]
[TD="class: xl67, bgcolor: transparent"]530.002
[/TD]
[TD="class: xl68, bgcolor: transparent"]409
[/TD]
[TD="class: xl69, bgcolor: transparent"]188
[/TD]
[TD="class: xl68, bgcolor: transparent"]930
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]6
[/TD]
[TD="class: xl67, bgcolor: transparent"]530.003
[/TD]
[TD="class: xl70, bgcolor: transparent"]#N/A
[/TD]
[TD="class: xl69, bgcolor: transparent"]530
[/TD]
[TD="class: xl68, bgcolor: transparent"]220
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]7
[/TD]
[TD="class: xl67, bgcolor: transparent"]534
[/TD]
[TD="class: xl68, bgcolor: transparent"]140
[/TD]
[TD="class: xl69, bgcolor: transparent"]530.001
[/TD]
[TD="class: xl68, bgcolor: transparent"]400
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]8
[/TD]
[TD="class: xl67, bgcolor: transparent"]534.002
[/TD]
[TD="class: xl68, bgcolor: transparent"]273
[/TD]
[TD="class: xl69, bgcolor: transparent"]530.002
[/TD]
[TD="class: xl68, bgcolor: transparent"]409
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]9
[/TD]
[TD="class: xl71, bgcolor: transparent"]534.003
[/TD]
[TD="class: xl72, bgcolor: transparent"]#N/A
[/TD]
[TD="class: xl69, bgcolor: transparent"]530.003
[/TD]
[TD="class: xl68, bgcolor: transparent"]4092
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]10
[/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"]530.004
[/TD]
[TD="class: xl68, bgcolor: transparent"]4900
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]11
[/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"]530.005
[/TD]
[TD="class: xl68, bgcolor: transparent"]4901
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]12
[/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"]530.006
[/TD]
[TD="class: xl68, bgcolor: transparent"]4902
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]13
[/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"]532
[/TD]
[TD="class: xl68, bgcolor: transparent"]220
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]14
[/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"]534
[/TD]
[TD="class: xl68, bgcolor: transparent"]140
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]15
[/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"]534.001
[/TD]
[TD="class: xl68, bgcolor: transparent"]272
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]16
[/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"]534.002
[/TD]
[TD="class: xl68, bgcolor: transparent"]273
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]17
[/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"]534.003
[/TD]
[TD="class: xl74, bgcolor: transparent"]277
[/TD]
[/TR]
</tbody>[/TABLE]

 

[britsgal]

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:


[TABLE="class: cms_table, width: 240"]
<tbody>[TR]
[TD="class: cms_table_xl76, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl76, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: cms_table_xl76, width: 64, bgcolor: transparent"]B[/TD]
[TD="class: cms_table_xl76, width: 64, bgcolor: transparent"]C[/TD]
[TD="class: cms_table_xl76, width: 64, bgcolor: transparent"]D[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl76, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl65, bgcolor: transparent"]value[/TD]
[TD="class: cms_table_xl66, bgcolor: transparent"]vlookup[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent, colspan: 2"]source data[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl76, bgcolor: transparent"]5[/TD]
[TD="class: cms_table_xl67, bgcolor: transparent"]530.002[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]409[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]188[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]930[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl76, bgcolor: transparent"]6[/TD]
[TD="class: cms_table_xl67, bgcolor: transparent"]530.003[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]#N/A[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]530[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]220[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl76, bgcolor: transparent"]7[/TD]
[TD="class: cms_table_xl67, bgcolor: transparent"]534[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]140[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]530.001[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]400[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl76, bgcolor: transparent"]8[/TD]
[TD="class: cms_table_xl67, bgcolor: transparent"]534.002[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]273[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]530.002[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]409[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl76, bgcolor: transparent"]9[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent"]534.003[/TD]
[TD="class: cms_table_xl72, bgcolor: transparent"]#N/A[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]530.003[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]4092[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl76, bgcolor: transparent"]10[/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl67, bgcolor: transparent"]530.004[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]4900[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl76, bgcolor: transparent"]11[/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl67, bgcolor: transparent"]530.005[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]4901[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl76, bgcolor: transparent"]12[/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl67, bgcolor: transparent"]530.006[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]4902[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl76, bgcolor: transparent"]13[/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl67, bgcolor: transparent"]532[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]220[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl76, bgcolor: transparent"]14[/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl67, bgcolor: transparent"]534[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]140[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl76, bgcolor: transparent"]15[/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl67, bgcolor: transparent"]534.001[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]272[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl76, bgcolor: transparent"]16[/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl67, bgcolor: transparent"]534.002[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]273[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl76, bgcolor: transparent"]17[/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl71, bgcolor: transparent"]534.003[/TD]
[TD="class: cms_table_xl74, bgcolor: transparent"]277[/TD]
[/TR]
</tbody>[/TABLE]

 

[joeu2004]

Starting Number: 530
Adding to starting number: .001
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:

[TABLE="class: grid, width: 749"]
<tbody>[TR]
[TD]
[/TD]
[TD]Internal Representation: Prev+0.001[/TD]
[TD]Internal Representation: Constant[/TD]
[/TR]
[TR]
[TD="align: right"]530.002[/TD]
[TD]530.001999999999,95270627550780773162841796875[/TD]
[TD]530.001999999999,95270627550780773162841796875[/TD]
[/TR]
[TR]
[TD="align: right"]530.003[/TD]
[TD]530.002999999999,929059413261711597442626953125[/TD]
[TD]530.003000000000,0427462509833276271820068359375[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]534.002[/TD]
[TD]534.001999999999,95270627550780773162841796875[/TD]
[TD]534.001999999999,95270627550780773162841796875[/TD]
[/TR]
[TR]
[TD="align: right"]534.003[/TD]
[TD]534.002999999999,929059413261711597442626953125[/TD]
[TD]534.003000000000,0427462509833276271820068359375[/TD]
[/TR]
</tbody>[/TABLE]

 

[britsgal]

Worked perfectly! Thanks so much! You were a big help!