I am trying to store a series of key/value pairs in a single cell in Google Sheets, then interrogate the array using formulae, such as VLOOKUP().
As an example, there is an array storing a series of key/value pairs:
{"keyA", "valueA"; "keyB", "valueB"; "keyC", "valueC"}
You can use VLOOKUP on this array if it is embedded in the formula:
=VLOOKUP("keyB",{"keyA","valueA";"keyB","valueB";"keyC","valueC"},2,FALSE)
which will return "valueB".
But if you store the array in a cell (eg B2) and refer to that cell in the formula, eg:
=VLOOKUP("keyB",B2,2,FALSE)
...you get a #REF! response with the detail: "Error. VLOOKUP evaluates to an out-of-bounds range."
Can anyone suggest a solution to this please?
Many thanks
There is no EVALUATE formula in Spreadsheet.
The array expression is some kind of formula, in order to output a range of values, you have to put:
={"keyA", "valueA"; "keyB", "valueB"; "keyC", "valueC"}
You could then do VLOOKUP on the output.
However, you can parse it yourself with SPLIT
=ArrayFormula(SPLIT(TRANSPOSE(SPLIT(REGEXREPLACE(B2,"^{""|""}$",),"""; """,FALSE)),""", """,FALSE))