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:

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

worksheet

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.

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »


Related Articles:


Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 2 Comment(s) on Table array hardcoding:

  1. Nice explaination of how to hardcode a table array. Another thing you can do is hardcode the array into a named constant using the Define Name dialog box. Then reusing the array in formulas becomes easier with less typing involved.

This article is closed to any future comments.
Excel School