0

After inserting a VLOOKUP formula in a Google spreadsheet, where the array parameter references columns 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.

|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
  • 1
    @pnuts - I don't see why you wouls say that its off-topic - nor why do you say that its un reproducable. however I did fail to accept the answer before and I have now corrected its. case closed :) – epeleg Jan 1 '15 at 10:01
0

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.

|improve this answer
1

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

See example file I created: VLOOKUP

|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
1

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.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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