Forum Discussion
Solved
Copy/paste value from formula leads to failed vlookup
I am trying to copy and paste values from a formula data set (RIGHT(,5) - get zip codes from addresses) into a separate sheet to then perform a vlookup. The vlookup returns with #N/A. The pasted values sit to the left of the cell, when I enter the cell and press enter the value moves to the right of the cell and then the vlookup works. The only way I can get this to work is entering every cell.
How can I can paste values ina
If you apply =RIGHT("abc12345",5) it returns text "12345". Copy/paste as value also paste text. As variant you may use =--RIGHT("abc12345",5) to return numbers as numbers.
Hi everyone,
If you are reading this post then you have an issue with VLookup. I spent hours trying to work out the problem.
For me the problem when copying down / pasting the VLookup formula as that my calculations were set to manual.
Go into excel and in the search bar type "Calculation" and see if Automatic is selected or if for some reason Manual is selected.
Change to Automatic and hopefully your VLoopup past will now work.- This was perfect and indeed was the issue. Thank you.
- Please try to wrap the lookup_value argument with VALUE function.
- utilizing a value function does work, thank you. I am still curious as to why the post paste special values formatting does not allow for a successful vlookup without adding a value function to the formula (annoying to need to add this to all future formulas).
Is there a solution or setting with the paste special values that would not require the value function in the lookup?
