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:

	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>

 

[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:

	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>

 

[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:

	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>

 

[britsgal]

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