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
5
  • Please share the docs with us (edit modus)
    – Jacob Jan Tuinstra
    Jan 6, 2013 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, 2013 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, 2013 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, 2013 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, 2015 at 10:01

3 Answers 3

Reset to default
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
4
  • This works for other cells not the cells that have the vlookup inside them.
    – epeleg
    Jan 6, 2013 at 20:38
  • @epeleg: example added
    – Jacob Jan Tuinstra
    Jan 6, 2013 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, 2013 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, 2013 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.