excel

Get an Excel Array Constant in One Cell

Getting an Excel Array Constant

Below is a screenshot capturing my data, vlookup formula, and lookup table. I need to convert month strings to numbers for my date formula so I have built up a DATE formula incorporating a VLOOKUP function that uses the first three characters from the month field (column B) to return the month number to the DATE formula, as shown.

2

If I want the same results without having to keep the extra table, I can do the following:

1. Select a convenient blank cell and type ‘=’ then either select the lookup data range excluding headers (K9:L20 in my screenshot), so my formula would read ‘=K9:L20’
2. Before exiting the cell, click Ctrl+Shift+Enter. This will exit the cell and store the formula as an array formula.
3. Re-select the cell (single-click on it) then press F2 to go into cell-edit mode then click F9 to trigger a manual calc on the sheet
4. This reveals the calculated array for the lookup table range you entered, in your cell–the string is actually a 2-dimensional array that can be deployed in the VLOOKUP formula as a normal range
5. Copy the array string, including curly brackets, then insert into the ‘table’ argument space of your vlookup formula, so the new vlookup section of my DATE formula (cell F2 in my screenshot) reads: VLOOKUP(LEFT(B2,3),{“Jan”, 1;”Feb”, 2;”Mar”, 3;”Apr”,4;”May”, 5;”Jun”, 6;”Jul”,7;”Aug”, 8;”Sep”, 9;”Oct”,10;”Nov”, 11; “Dec”, 12},2, FALSE)
6. Press enter and you’re done. You can now scrap that pesky lookup table. See my screenshot below if you’re not following..

3

 

The Benefits of this Technique

If you use Excel a lot for data modelling or analysis you will no doubt utilize the VLOOKUP function a lot, since it Excel’s way of joining datasets together. Sometimes you have a small 5-10 row reference table that simply translates one set of values to another… for example, you have data with a month_string column but you need the month number for use in a DATE function. So you create your mini-table similar to the one shown below, then use a vlookup function to return the monthno associated value for each row in your dataset.

1

It can be annoying having to store these little lookup tables in cell ranges in your workbook–especially if they are pretty trivial like this one. The above is a way to very quickly store the table (or 2 dimensional array, same thing) in the VLOOKUP formula as an Excel array constant so you can get rid of the lookup table and keep your analysis and models looking tidy.

Did you try this? Do you have any other applications of the technique to share. If so, feel free to leave a comment below.

2 thoughts on “Get an Excel Array Constant in One Cell”

  1. Nice! I will definitely be using this for short lookups, such as converting calendar month to fiscal year month (i.e. July = month 1). I have a date in A2. Here’s my formula in cell B2:
    =VLOOKUP(MONTH(A2),{1,7;2,8;3,9;4,10;5,11;6,12;7,1;8,2;9,3;10,4;11,5;12,6},2,FALSE)

Share your thoughts below :)