Vlookup not finding value when using TRUNC function

[TimberR6]

Has anyone had an issue with or know how to resolve an issue where VLOOKUP returns an #NA for a value that is known to exist in the array when the lookup_value is the output of a TRUNC function.

Basically I have...
column E as a percentage ie. E3 = 35.7278134665715% and
column F is using the TRUNC function to just return the integer (I cant have the integer be rounded up or down) so F3 is =TRUNC(E3,2)*100. I am multiplying the value by 100 as my array for the vlookup is using integers 50-500.
Column G is my VLOOKUP function so G3 is =IF(F3>=50,VLOOKUP(F3,'sheet3'!$A$17:$AB$359,4,0),0)

I have 25 rows of values in column E to check... my formula in G3 works and returns the correct value from the array, but when I drag the formula down there are 3 values the VLOOKUP function will not find and returns #NA.

these are my columns and formulas: (sorry I cant figure out how to make it look more Excelish when it posts)

0.357278134665715

​

=TRUNC(E3,2)*100

​

=IF(F3>=50,VLOOKUP(F3,'Sheet3'!$A$17:$AB$359,4,0),0)
1.73578285867095

​

=TRUNC(E4,2)*100

​

=IF(F4>=50,VLOOKUP(F4,'Sheet3'!$A$17:$AB$359,4,0),0)
1.23708023632552

​

=TRUNC(E5,2)*100

​

=IF(F5>=50,VLOOKUP(F5,'Sheet3'!$A$17:$AB$359,4,0),0)
0.558139534883721

​

=TRUNC(E6,2)*100

​

=IF(F6>=50,VLOOKUP(F6,'Sheet3'!$A$17:$AB$359,4,0),0)
0.841389578163772

​

=TRUNC(E7,2)*100

​

=IF(F7>=50,VLOOKUP(F7,'Sheet3'!$A$17:$AB$359,4,0),0)
0.642298342541437

​

=TRUNC(E8,2)*100

​

=IF(F8>=50,VLOOKUP(F8,'Sheet3'!$A$17:$AB$359,4,0),0)
0.340023612750885

​

=TRUNC(E9,2)*100

​

=IF(F9>=50,VLOOKUP(F9,'Sheet3'!$A$17:$AB$359,4,0),0)

These are the results of the same cells:

35.728% 35 $0.00
173.578% 173 $21,000.00
123.708% 123 $9,500.00
55.814% 55 #N/A
84.139% 84 $3,950.00
64.230% 64 $2,950.00
34.002% 34 $0.00



Can anyone tell me why VLOOKUP cant find the "55" value? I can see it clear as day in the 1st column of my array????


THANK YOU FOR YOUR HELP!!

 

[Jonmo1]

Does it find the 55 if you manually enter it into the formula?

VLOOKUP(55,'Sheet3'!$A$17:$AB$359,4,0)

 

[TimberR6]

Sorry I meant to add that to my original message...yes, if I manually enter 55 or any other of the #NA values the vlookup will fine the values in the array.

 

[Jonmo1]

I would suspect a rounding issue related to floating point precision issues documented here.
https://support.microsoft.com/en-us/kb/78113

Common solution is to round results of calcluations
Try
=ROUND(TRUNC(E3,2)*100,0)

 

[TimberR6]

That seems to work perfectly so far!! Thank you for the quick and accurate solution!

 

[Jonmo1]

You're welcome.