Table array hardcoding

Here's something I thought might be useful, but I'm not yet sure how.

As you know, VLOOKUP's second argument is a table array.

Table_array is a table of text, numbers, or logical values, in which data is retrieved. Table_array can be a reference to a range or a range name.

Well, it can also be a block of literal values, properly formatted.

For example, I put the following values on a new worksheet:

To look up a value in A1, I might use this formula:

=VLOOKUP(A1,D1:E4,2,FALSE)

But I don't feel like using a range today, and I know my list of possible values isn't going to change (hey, this is my experiment). So I hardcode the list of values directly into the formula like this:

=VLOOKUP(A1,{"A","Apples";"O","Oranges";"P","Peaches";"C","Cherries"},2,FALSE)

Rules:

• The list of lookup values must be surrounded by braces ("{ }").
• Use commas to change columns and semicolons to indicate new rows, to simulate the row/column approach of a table array.

So, in formula terms, {"A","Apples";"O","Oranges";"P","Peaches";"C","Cherries"} is equivalent to

because the comma means "next column" and the semicolon means "next row".

Normally I wouldn't do this, but the list of lookup values is short, and I'm bored.