VLOOKUP Only Works with Paste Values

[Kolchak74]

Someone asked me to do some updates to a project management file they inherited and I'm running into issues with VLOOKUP.

I have a list of task ID numbers in column A that are set up like this: Look at the cell above and add .01 to it. The result is a a list of numbers 3.01, 3.02, 3.03, etc.

Column E is where I manually type in one of the task IDs and Column F contains the the VLOOKUP.

I know the formula itself works because if I use the Paste Value option to paste a number from Column A into the lookup cell the formula works fine, but if I manually type in the number from Column A, I get #NA.

I tried testing by with =E25=A24, for example, to make sure the values were in fact the same and I get "TRUE". Also, if I manually enter the number in Column A, the formula works.

I'm not sure where to go next. Any ideas?

 

[Kolchak74]

Ok...latest update. I figured out something else. If I manually type in, for example, 3.01, 4.01, 5.01, the lookup works. If I enter any other number besides one ending in .01, it doesn't work.

 

[Kolchak74]

One more update...the formulas work fine in the first section where all the numbers are 1.01, 1.02,1.03 and so one. The problem doesn't start until I get to the second section.

 

[pjmorris]

is one of the numbers formatted as Text thats the most likely. ALternatively can you use xl2bb (link on the board) to copy a sample of your data including the table from which the data is 'looked up'.

 

[Kolchak74]

webQS-Excel-Pack-Project-Schedule.xlsm
	A	B	C	D	E	F	G
10	P	PROJECT LENGTH				1/4/2021	#N/A
11	1	Site establishment	Task Group			1/4/2021	1/25/2021
12	1.01	Task 1	Task	Incl w/end	None	1/4/2021	1/7/2021
13	1.02	Task 2	Task	Incl w/end	1.01	1/8/2021	1/10/2021
14	1.03	Task 2	Task	Incl w/end	1.01	1/8/2021	1/10/2021
15	1.04	Task 2	Task	Incl w/end	1.01	1/8/2021	1/10/2021
16	1.05	Task 2	Task	Incl w/end	1.04	1/11/2021	1/13/2021
17	1.06	Task 2	Task	Incl w/end	1.05	1/14/2021	1/16/2021
18	1.07	Task 3	Task	Incl w/end	1.06	1/17/2021	1/19/2021
19	1.08	Task 3	Task	Incl w/end	1.07	1/20/2021	1/22/2021
20	1.09	Task 9	Task	Incl w/end	1.08	1/23/2021	1/25/2021
21	2	Task Group Title	Task Group			#N/A	#N/A
22	2.01	Task 1	Task	Incl w/end	1.01	1/8/2021	1/10/2021
23	2.02	Task 2	Task	Incl w/end	2.01	1/11/2021	1/13/2021
24	2.03	Task 3	Task	Incl w/end	2.02	#N/A	#N/A
25	2.04	Task 4	Task	Incl w/end	2.03	#N/A	#N/A
26	2.05	Task 5	Task	Incl w/end	2.04	#N/A	#N/A
Project Programme

Cell Formulas
Range	Formula
G10	G10	=IF(ISBLANK(F10),0,MAX(IF(G12:G9690<>0,G12:G9690)))
F11	F11	=IF(ISBLANK(F12),0,MIN(IF(F12:F20<>0,F12:F20)))
G11	G11	=IF(ISBLANK(G12),0,MAX(IF(G12:G20<>0,G12:G20)))
F12:F20	F12	=IF(ISBLANK(E12),0,IF(E12="None",$F$10,IF(D12="Mon-Fri Only",WORKDAY(VLOOKUP(E12,$A$10:$G$73,7,FALSE),1),(VLOOKUP(E12,$A$10:$G$73,7,FALSE)+1))))
G12:G20,G22:G26	G12	=IF(ISBLANK(E12),0,IF(H12=0,F12,IF(D12="Mon-Fri Only",WORKDAY(F12,H12-1,0),F12+H12-1)))
F21	F21	=IF(ISBLANK(F22),0,MIN(IF(F22:F26<>0,F22:F26)))
G21	G21	=IF(ISBLANK(G22),0,MAX(IF(G22:G26<>0,G22:G26)))
F22:F26	F22	=IF(ISBLANK(E22),0,IF(E22="None",$F$10,VLOOKUP(E22,$A$10:$G$73,7,FALSE)+1))
A12:A20	A12	=A11+0.01
A22:A26	A22	=A21+0.01+0

Cells with Data Validation
Cell	Allow	Criteria
C11:C73	List	='dropdowns (2)'!$I$2:$I$4
D11	List	='dropdowns (2)'!$I$2:$I$4
D12:D43	List	='dropdowns (2)'!$G$2:$G$3

 

[joeu2004]

I have a list of task ID numbers in column A that are set up like this: Look at the cell above and add .01 to it. The result is a a list of numbers 3.01, 3.02, 3.03, etc. [....] I know the formula itself works because if I use the Paste Value option to paste a number from Column A into the lookup cell the formula works fine, but if I manually type in the number from Column A, I get #NA.

No, the formula does not work. And your paste-value experiment proves it.

The short answer is: if you enter 3.01 into A1, the subsequent formulas should be of the form =ROUND(A1+0.01, 2).

To demonstrate, =VLOOKUP(3.01+0.01, {3.02}, 1, 0) returns #N/A, but =VLOOKUP(ROUND(3.01+0.01, 2), {3.02}, 1, 0) returns 3.02.

In general, whenever we expect a calculation that involves decimal fractions (including division of integers) to be accurate to some number of decimal places, we should explicitly round to that number of decimal places -- and not to an arbitrary number of decimal places like 10, as some people suggest.

The root cause of the problem is: Excel uses 64-bit binary floating-point to represent numbers internally, and most decimal fractions cannot be converted to that binary form exactly.

Moreover, a particular decimal fraction might be approximated differently in that binary form, depending on the magnitude of the number. That is the reason, for example, why IF(10.01-10 = 0.01, TRUE) returns FALSE(!).

 

[joeu2004]

I tried testing by with =E25=A24, for example, to make sure the values were in fact the same and I get "TRUE"

This is an unfortunate consequence of tricks that Excel plays in a misguided attempt to hide the infinitesimal residuals that often result from binary floating-point arithmetic.

In a nutshell, the comparison operators ("=", ">", etc) round their operands to 15 significant digits, just for the purpose of the comparison. In contrast, most functions compare the exact binary representation.

Consequently, E25=A24 might return TRUE, but ISNUMBER(MATCH(E25,A24,0)) might return FALSE.

Also, =E25-A24 might return exact zero (0.00E+00 when formatted as Scientific), but E25-A24-0 might return an infinitesimal difference like 1.11E-16, and IF(E25-A24=0, TRUE) might return FALSE(!).

 

[joeu2004]

Look at the cell above and add .01 to it. The result is a a list of numbers 3.01, 3.02, 3.03, etc

If I manually type in, for example, 3.01, 4.01, 5.01, the lookup works

the formulas work fine in the first section where all the numbers are 1.01, 1.02,1.03 and so one. The problem doesn't start until I get to the second section

Sorry for the incessant responses. But you keep changing the examples.

For the last example, which matches your code snippet, starting with 1 in A11 and adding 0.01 through 1.09 in A20 works only by coincidence.

Starting with 2 in A21 and adding 0.01 through 2.05 in A26, we begin to see infinitesimal residuals in A23 (2.02). See below.

It is difficult to predict when such anomalies might arise. They might come and go as we add 0.01 (or whatever) down the list.

Book1
	A	B	C
11	1	0.00E+00	0.00E+00
12	1.01	0.00E+00	0.00E+00
13	1.02	0.00E+00	0.00E+00
14	1.03	0.00E+00	0.00E+00
15	1.04	0.00E+00	0.00E+00
16	1.05	0.00E+00	0.00E+00
17	1.06	0.00E+00	0.00E+00
18	1.07	0.00E+00	0.00E+00
19	1.08	0.00E+00	0.00E+00
20	1.09	0.00E+00	0.00E+00
21	2	0.00E+00	0.00E+00
22	2.01	0.00E+00	0.00E+00
23	2.02	-4.44E-16	0.00E+00
24	2.03	-4.44E-16	0.00E+00
25	2.04	-8.88E-16	0.00E+00
26	2.05	-8.88E-16	0.00E+00
Sheet1

Formulas:
A11: 1
A12: =A11+0.01
A21: 2
A22: =A21+0.01
B11: =SUM(A11,-(A11&""))
C11: =A11-(A11&"")

In column B, the formula =SUM(A11,-(A11&"")) exposes any residuals, especially when formatted as Scientific.

It subtracts the value rounded to 15 significant digits (A11&"") from the exact binary value (A11). With A11&"", we take advantage of the fact that Excel formats (displays) only up to the first 15 signficant digits, rounded.

In this context, the SUM function does not apply the trick that I described previously, to wit: for =E25-A24, Excel might return exact zero (0.00E+00). But beware: in other contexts, the SUM function does apply the trick(!).

In contrast, the simple subtraction in column C does apply the trick, giving results that might be misleading in some contexts (like your problem).

 

[pjmorris]

Put this

=IF(ISBLANK(E22),0,IF(E22="None",$F$10,SUMPRODUCT(--($A$12:$A21=E22),($G$12:$G21))+1))

in cell F22 and copy down. The sumproduct doesnt seem to suffer the same inconsistencies as either VLOOKUP or MATCH was causing. If you want to get an idea about how it works use 'Evaluate Formula' on the Formula Auditing tab of the Formula ribbon.

HTH

 

[Kolchak74]

Thanks everyone. Using the ROUND function was successful. Thanks also for the background information as to why I was having the issue.