VLOOKUP Only Works with Paste Values

Kolchak74

New Member
Joined
Feb 4, 2021
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
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?
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Kolchak74

New Member
Joined
Feb 4, 2021
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Feb 4, 2021
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Aug 2, 2012
Messages
1,988
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Feb 4, 2021
Messages
6
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

webQS-Excel-Pack-Project-Schedule.xlsm
ABCDEFG
10PPROJECT LENGTH1/4/2021#N/A
111Site establishmentTask Group1/4/20211/25/2021
121.01Task 1TaskIncl w/endNone1/4/20211/7/2021
131.02Task 2TaskIncl w/end1.011/8/20211/10/2021
141.03Task 2TaskIncl w/end1.011/8/20211/10/2021
151.04Task 2TaskIncl w/end1.011/8/20211/10/2021
161.05Task 2TaskIncl w/end1.041/11/20211/13/2021
171.06Task 2TaskIncl w/end1.051/14/20211/16/2021
181.07Task 3TaskIncl w/end1.061/17/20211/19/2021
191.08Task 3TaskIncl w/end1.071/20/20211/22/2021
201.09Task 9TaskIncl w/end1.081/23/20211/25/2021
212Task Group TitleTask Group#N/A#N/A
222.01Task 1TaskIncl w/end1.011/8/20211/10/2021
232.02Task 2TaskIncl w/end2.011/11/20211/13/2021
242.03Task 3TaskIncl w/end2.02#N/A#N/A
252.04Task 4TaskIncl w/end2.03#N/A#N/A
262.05Task 5TaskIncl w/end2.04#N/A#N/A
Project Programme
Cell Formulas
RangeFormula
G10G10=IF(ISBLANK(F10),0,MAX(IF(G12:G9690<>0,G12:G9690)))
F11F11=IF(ISBLANK(F12),0,MIN(IF(F12:F20<>0,F12:F20)))
G11G11=IF(ISBLANK(G12),0,MAX(IF(G12:G20<>0,G12:G20)))
F12:F20F12=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:G26G12=IF(ISBLANK(E12),0,IF(H12=0,F12,IF(D12="Mon-Fri Only",WORKDAY(F12,H12-1,0),F12+H12-1)))
F21F21=IF(ISBLANK(F22),0,MIN(IF(F22:F26<>0,F22:F26)))
G21G21=IF(ISBLANK(G22),0,MAX(IF(G22:G26<>0,G22:G26)))
F22:F26F22=IF(ISBLANK(E22),0,IF(E22="None",$F$10,VLOOKUP(E22,$A$10:$G$73,7,FALSE)+1))
A12:A20A12=A11+0.01
A22:A26A22=A21+0.01+0
Cells with Data Validation
CellAllowCriteria
C11:C73List='dropdowns (2)'!$I$2:$I$4
D11List='dropdowns (2)'!$I$2:$I$4
D12:D43List='dropdowns (2)'!$G$2:$G$3
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,007
Office Version
  1. 2010
Platform
  1. Windows
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(!).
 
Solution

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,007
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Mar 2, 2014
Messages
3,007
Office Version
  1. 2010
Platform
  1. Windows
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.

Rich (BB code):
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

Well-known Member
Joined
Aug 2, 2012
Messages
1,988
Office Version
  1. 2016
Platform
  1. Windows
Put this
Excel Formula:
=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

New Member
Joined
Feb 4, 2021
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
Thanks everyone. Using the ROUND function was successful. Thanks also for the background information as to why I was having the issue.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,044
Messages
5,622,381
Members
415,894
Latest member
silverhaze

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top