Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

After inserting a vlookup formula in a Google spreadsheet, where the array parameter references acolumns of a pivot table, how do I edit the formula again?

When I enter the formula, it just displays the value returned by the formula, and I can't seem to find a way to view or edit the original vlookup formula.

share|improve this question

migrated from superuser.com Jan 6 '13 at 20:23

This question came from our site for computer enthusiasts and power users.

    
Please share the docs with us (edit modus) –  Jacob Jan Tuinstra Jan 6 '13 at 20:39
    
sorry, I can't data is sensitive. however it must be something with my specific worksheet as when I tried to create a sample one showing the same issue it just worked. maybe related to my having hebrew names to the sheets or the vlookup being across sheets, I will try to create a smaple. –  epeleg Jan 6 '13 at 20:43
    
I was finally able to replicate the issue on a test worksheet. The missing part was that this happens only when the array parameter of the VLOOKUP formula references columns of a pivot table. –  epeleg Jan 7 '13 at 9:02
    
And Yet another UPDATE: The same happens if you use =Indirect("B1") where B1 is inside a pivot table. –  epeleg Jan 7 '13 at 9:08

3 Answers 3

Press F2 (when in cell) on the keyboard or double click in the cell itself.

See example file I created: VLOOKUP

share|improve this answer
    
This works for other cells not the cells that have the vlookup inside them. –  epeleg Jan 6 '13 at 20:38
    
@epeleg: example added –  Jacob Jan Tuinstra Jan 6 '13 at 20:47
    
As I already commented on the Q. I realize that this is not a general issue. but still it does happen to me. I will try to create an example that will demonstrate the issue. –  epeleg Jan 7 '13 at 6:59
    
I was finally able to replicate the issue on a test worksheet. The missing part was that this happens only when the array parameter of the VLOOKUP formula references columns of a pivot table. – epeleg just now –  epeleg Jan 7 '13 at 9:02

Based on Prakashr's answer:

If you are looking up a number, then a workaround is to convert the result into a numerical value:

Instead of using something like

=VLOOKUP(A2,BOM!A:B, 2, 0)  

Use

=VALUE(VLOOKUP(A2,BOM!A:B, 2, 0))

Also, if you already have a vlookup you may need to delete it and then re-enter it.

share|improve this answer

I had this same issue. I did a workaround which seems to work fine for me.

Instead of using something like

=vlookup(A2,BOM!A:B, 2,0)  

I just added a +0 at the end as my lookup was returning an integer.

So when I put

=vlookup(A2,BOM!A:B, 2,0)+0 

it works fine for me and my vlookup is now editable.

Also, if you already have a vlookup you may need to delete it and then reenter it.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.