Excel using vlookup not calculating after 3 steps in spreadsheet---#NAs start "pop up"

Copper Contributor

It is a workbook I created years ago. Have edited it adding different functions. I check formulas back to 2005 and all continue to work. But stops working in 2013- 2014. Earlier copies are in "Compatibility" mode currently they are not. 

Win 10

Office 365

Excel 2016

How do I avoid all the #NAs if the all the columns don't return information from the Rates sheet.  I have tried " ", "0",and 0. Still get #NAs,  and it is messing my totals up. Sum won't work with #NAs.....

7 Replies

Hi,

 

Would you be able to indicated which sheet and cells you are having trouble with the VLOOKUP? The spreadsheet you attached is very large making it difficult to pinpoint the issue.

 

Thanks

Hello,

 

This is not an issue with formulas not working, but with data entry.

 

Looking at the formula in Sheet "Computations", cell B8. Unravelling the formula, it turns out that the value "3X" is being looked up with VLookup in a table that shows "3X" as a lookup value, but the actual content of the cell is just a number 3 that has been formatted with a custom format to appear as "3X". 

 

The value ultimately comes from your Input form, where the value "3X" has been entered into cell B7.

The cell is formatted to show numbers with an "X" following, so the "X" does not need to be typed into this cell.  If you simply type the number 3 into this cell, it will still appear as "3X" because of the formatting and all the follow-on formulas will calculate correctly.

 

Let me know if that helps. 

 

 

 

Thanks

I reformatted cell B4 to just a general number and that is what is displayed.  Now all the #NAs are gone the all the calculations are correct.  I would like for B4 to display as 3X and not just a 3.... from 2005 up to 2015, that is the way it worked.  I just did a custom format to save a keystroke. Oh well.

 

Again, thanks for the help.

You need to understand the difference between a cell's value and a cell's format.

 

The formulas totally ignore the cell format and will only evaluate the cell value, i.e. the underlying value "3" in this case.

 

You mix up presentation and data in your setup and that can be confusing. 

 

Make sure that people only enter a "3" and not a "3X". Because your formulas only work on the value `3`, which in some scenarios has been formatted to display as a 3X. 

 

That means that you can format cell B4 with the custom format to display any number with a trailing "X", but the actual value of the cell is still just the number that was entered. 

 

Make sure to use data validation so only numbers can be entered into the cell. That will prevent the confusion that arises when people enter the text value "3X" into the cell.

 

The 3X is converted to a 3 on the Rate page in the section of Frequency Conversion to indicated which column to select the rate amount for the Column A 

The computations page. Cell B7----=IF('Input Form'!B5>=0,VLOOKUP(B3,'Rates 2019'!$A$3:$G$37,VLOOKUP(B4,'Rates 2019'!$J$3:$K$7,2,FALSE)+1,FALSE),"0")

 

This formula has worked since 2005. Since I started using Office 365 - Excel 2016, it has not worked.

I have tried it with and without the ##X format.  Still does not work.