Google Sheets – Editing a VLOOKUP Formula Referencing a Pivot Table

google sheetsvlookup

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.

Best 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.

Related Solutions

Google-sheets – Google Sheets: can’t sort table that use INDEX and MATCH

When ISNUMBER(SEARCH("My Search",'Form Responses 1'!$B$2:$B)) is TRUE the row values are not dependent on the row values of the column A, so sorting column A will not sort the value in column D for this cases.

To be display the sorted values, use SORT() or QUERY() instead of sorting in place.

An alternative is to copy and paste the values of Column D before sorting.

The same could be applied to Column E.

Google Sheets – VLOOKUP Problem with Value from ArrayFormula and RegexExtract

There is a difference between the string of characters "100103" and the number 100103. Your workshops tab contains numbers. regexextract returns a string. To make things work, convert string to number using value after regexextract:

=arrayformula(iferror(value(regexextract(regexextract(F2:F, "ID#\d+"), "\d+"))))

Best Answer

Related Solutions

Google-sheets – Google Sheets: can’t sort table that use INDEX and MATCH

Google Sheets – VLOOKUP Problem with Value from ArrayFormula and RegexExtract

Related Topic