Copy/paste value from formula leads to failed vlookup

[adamroot]

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  

[#2]

Please try to wrap the lookup_value argument with VALUE function.

[#3]

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?

[#4 · Accepted solution]

@adamroot 

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.

[#5]

After the initial paste, perform another paste like this:
1. Enter 1 in a cell outside the paste area.
2. Select the cell with 1 in it and press Ctrl+C to copy it to the clipboard.
3. Select the paste area.
4. Press Ctrl+Alt+V,M,Enter.
5. Press Esc.
6. Delete the content of the cell you entered 1 earlier.

[#6]

@adamroot 

One more variant select both Value and Add

If with shortcuts

Ctrl+Alt,V, D, Enter

[#7]

I like the simplicity of this option. Thanks! Works like a gem and doesn't impact additional formulas/cells.

[#8]

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.

[#9]

This was perfect and indeed was the issue. Thank you.