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.
Random Data Generator