SOLVED
Home

Copy/paste value from formula leads to failed vlookup

%3CLINGO-SUB%20id%3D%22lingo-sub-900070%22%20slang%3D%22en-US%22%3ECopy%2Fpaste%20value%20from%20formula%20leads%20to%20failed%20vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-900070%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20copy%20and%20paste%20values%20from%20a%20formula%20data%20set%20(RIGHT(%2C5)%20-%20get%20zip%20codes%20from%20addresses)%20into%20a%20separate%20sheet%20to%20then%20perform%20a%20vlookup.%20The%20vlookup%20returns%20with%20%23N%2FA.%20The%20pasted%20values%20sit%20to%20the%20left%20of%20the%20cell%2C%20when%20I%20enter%20the%20cell%20and%20press%20enter%20the%20value%20moves%20to%20the%20right%20of%20the%20cell%20and%20then%20the%20vlookup%20works.%20The%20only%20way%20I%20can%20get%20this%20to%20work%20is%20entering%20every%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20can%20paste%20values%20ina%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-900070%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-900086%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%2Fpaste%20value%20from%20formula%20leads%20to%20failed%20vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-900086%22%20slang%3D%22en-US%22%3EPlease%20try%20to%20wrap%20the%20lookup_value%20argument%20with%20VALUE%20function.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-900103%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%2Fpaste%20value%20from%20formula%20leads%20to%20failed%20vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-900103%22%20slang%3D%22en-US%22%3Eutilizing%20a%20value%20function%20does%20work%2C%20thank%20you.%20I%20am%20still%20curious%20as%20to%20why%20the%20post%20paste%20special%20values%20formatting%20does%20not%20allow%20for%20a%20successful%20vlookup%20without%20adding%20a%20value%20function%20to%20the%20formula%20(annoying%20to%20need%20to%20add%20this%20to%20all%20future%20formulas).%3CBR%20%2F%3E%3CBR%20%2F%3EIs%20there%20a%20solution%20or%20setting%20with%20the%20paste%20special%20values%20that%20would%20not%20require%20the%20value%20function%20in%20the%20lookup%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-900130%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%2Fpaste%20value%20from%20formula%20leads%20to%20failed%20vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-900130%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F422283%22%20target%3D%22_blank%22%3E%40adamroot%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20apply%20%3DRIGHT(%22abc12345%22%2C5)%20it%20returns%20text%20%2212345%22.%20Copy%2Fpaste%20as%20value%20also%20paste%20text.%20As%20variant%20you%20may%20use%20%3D--RIGHT(%22abc12345%22%2C5)%20to%20return%20numbers%20as%20numbers.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-900141%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%2Fpaste%20value%20from%20formula%20leads%20to%20failed%20vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-900141%22%20slang%3D%22en-US%22%3EAfter%20the%20initial%20paste%2C%20perform%20another%20paste%20like%20this%3A%3CBR%20%2F%3E1.%20Enter%201%20in%20a%20cell%20outside%20the%20paste%20area.%3CBR%20%2F%3E2.%20Select%20the%20cell%20with%201%20in%20it%20and%20press%20Ctrl%2BC%20to%20copy%20it%20to%20the%20clipboard.%3CBR%20%2F%3E3.%20Select%20the%20paste%20area.%3CBR%20%2F%3E4.%20Press%20Ctrl%2BAlt%2BV%2CM%2CEnter.%3CBR%20%2F%3E5.%20Press%20Esc.%3CBR%20%2F%3E6.%20Delete%20the%20content%20of%20the%20cell%20you%20entered%201%20earlier.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-900152%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%2Fpaste%20value%20from%20formula%20leads%20to%20failed%20vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-900152%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F422283%22%20target%3D%22_blank%22%3E%40adamroot%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOne%20more%20variant%20select%20both%20Value%20and%20Add%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20399px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F135899i5F7FC02858177289%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIf%20with%20shortcuts%3C%2FP%3E%0A%3CP%3ECtrl%2BAlt%2CV%2C%20D%2C%20Enter%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-900159%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%2Fpaste%20value%20from%20formula%20leads%20to%20failed%20vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-900159%22%20slang%3D%22en-US%22%3EI%20like%20the%20simplicity%20of%20this%20option.%20Thanks!%20Works%20like%20a%20gem%20and%20doesn't%20impact%20additional%20formulas%2Fcells.%3C%2FLINGO-BODY%3E
adamroot
New Contributor

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  

6 Replies
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?
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.

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.

@adamroot 

One more variant select both Value and Add

image.png

If with shortcuts

Ctrl+Alt,V, D, Enter

I like the simplicity of this option. Thanks! Works like a gem and doesn't impact additional formulas/cells.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
217 Replies