Friday, March 4, 2016

VLOOKUP returning #N/A when it shouldn't

I am using VLOOKUP to convert rounded decimals to mixed fractions up to 1/32nds (e.g.
1.313 => 1-5/16).  I have quite a few decimals and a bunch of them are working correctly, but some of them are returning #N/A when they shouldn't.
 
This is my formula:
=IF(INT(EM37)>0,INT(EM37),"") & IF(EM37-INT(EM37)>0, IF(INT(EM37)>0,"-","") & VLOOKUP(EM37-INT(EM37),Lookup!$B$145:$C$175,2,FALSE),"")
 
After doing a bit of research on VLOOKUP problems, the pattern seems to be conflicting data types between lookup_value and the first column of table_array.  I have verified that ISNUMBER returns TRUE for both.
 
Opening "Show Calculation Steps" from the error menu that appears next to the #N/A cell for EM37=1.313, the following is shown:
"1" & IF(TRUE, "-" & VLOOKUP(0.313,Lookup!$B$145:$C$175,2,FALSE), "")
The next evaluation will result in an error.
 
Here is B154:C154, which is within table_array:
0.313    5/16
 
Why is #N/A being returned???

Anwsers to the Problem VLOOKUP returning #N/A when it shouldn't

Download Error Fixer (Free)

bwaldmann wrote:
I am using VLOOKUP to convert rounded decimals to mixed fractions up to 1/32nds (e.g.
1.313 => 1-5/16).
[....]
This is my formula:
[....] VLOOKUP(EM37-INT(EM37),Lookup!$B$145:$C$175,2,FALSE) [....]
[....]
Here is B154:C154, which is within table_array:
0.313    5/16
Why is #N/A being returned?
Perhaps because the value EM37-INT(EM37) does not __exactly__ match the binary representation of the constant 0.313, and you are doing an an "exact" VLOOKUP (FALSE).
To confirm, try entering the following formula  exactly  as written (with "redundant" +0"):
=EM37-INT(EM37)-Lookup!B154+0
Format as Scientific.  Is the result 0.00E+00 (exactly zero) or something else?
If you continue to use this design, you should sort B145:C175 in ascending order based on column B, and use "approximate" VLOOKUP with TRUE in the last parameter.  It is more efficient, too.
But even then, you might get bitten because EM37-INT(EM37) might  appear 
to be 0.313, but it is actually infinitesimally less than the binary representation of the constant 0.313.
To avoid that, use ROUND(EM37-INT(EM37),3) if the constants in B145:B175 are always rounded to 3 decimal places.
If not, round to the greatest precision represented by the constants in B145:B175.

Because of this VLOOKUP returning #N/A when it shouldn't error, Windows can fail in starting some processes and the system's performance can significantly slow down. In some cases, Windows has problems starting up and the system can freeze.

Lastly, this error VLOOKUP returning #N/A when it shouldn't can even result in the Blue screen of death error message, just like other critical Windows errors. If any of the above problems and symptoms occur in your PC, then you should immediately seek to resolve this problem.

In order to resolve VLOOKUP returning #N/A when it shouldn't error or repair the corrupted and damaged Windows files, you will need a VLOOKUP returning #N/A when it shouldn't error repair tool. There is more than one method of treating this kind of error code, and here is how you can manually do it:

  1. Start the computer and log in as administrator
  2. Go to the start button and select All Programs. Go to Accessories, System Tools, and then System Restore
  3. Click on System Restore and in a new window, select the 'Restore my computer to an earlier time's option and click Next
  4. On the new confirmation window that appears, click Next
  5. Restart your computer after the restoration has been completed

Another Safe way to Repair the Problem: VLOOKUP returning #N/A when it shouldn't:

 

 

How to Fix VLOOKUP returning #N/A when it shouldn't with SmartPCFixer?

1. Click the button to download Error Fixer . Install it on your system.  Run it, and it will scan your computer. The junk files will be shown in the list.

2. After the scan is finished, you can see the errors and problems which need to be fixed.

3. The Fixing part is done, the speed of your computer will be much higher than before and the errors have been fixed.


Related: 1080p setting is gone in Windows Movie Maker After Windows8 1 KB2919355 x64 update installation having issue with the new windows start button and the menu search share start devices and settings is not showing anymore cannot access xp files on my external hd using Windows 7 Automatic Logon to Windows SharePoint Server C Windows svchost exe error message pops up everytime C Users user name AppData Roaming Microsoft Windows Start Menu Programs Startup Autoruns Disabled Computer freezes on boot up on both Windows Vista and after a fresh install of Windows 7 Cannot write message using Outlook Express and Windows Live Mail Daily BSOD s at random on Windows 7 x64 Computer with Windows XP will not recognize Comupluter on Network with Windows 7 Windows 7 sees Computers woth XP but cannont communicate with them Can Microsoft make a Windows 8 only for desktops laptops without the touch screen features Dell Inspiron 15 with Windows 7 having Startup issue with flicking Charms in Windows 8 1 Difference between Windows 7 Pro 32 64 bit to Windows 7 Pro OEM version Bing tabs on Windows Internet Explorer 8 DJ Inkers will not work on Windows 7 Is there a way to make it work,Can favorites be added to my Yahoo toolbar How I m using IE 8 0 6 and Windows 8 ati radeon hd 3200 graphics driver update How can I disable restarting of applications after Windows Update performs a reboot ati radeon hd 4800 driver PC crashed during Windows Experience assessment ati radeon drivers download Share Folders ain t accessible between three Windows 7 Ultimate Computers Pls Help ATI Display Driver Starting Problem with Windows 7 no icons appear ati radeon driver download Windows 7 icons there but doesn t display any words ati Windows old recovery of previous programs creative labs drivers four Make use of the “Tab” critical within the keyboard to select the USB flash drive.
Read More: [Solution] how do I print an e-mail without all the files on the left hand side of the screen printing too and I lose half of my text on the message,How to Resolve - web cam 1.3mp?,-windows 8 change a document from chinese language? [Solved],How to Resolve - Computer says i cannot access Microsoft Security Essntials in safe mode,how do i exit safe mode?,Header not showing using IE & google chrome,Windows 7 Failed to boot with Blue Screen Error.,registry edit or back up,How does my hard drive get fragmented?,Is it possible to shut the lid of your laptop, and not have your computer go into sleep or hibernation?

No comments:

Post a Comment