The arraylookup( function uses a key to look up a value in a two dimensional text array containing key/value pairs (see Text Arrays).


This function has five parameters:

array – is the text of the array. This array must be “two-dimensional”, which means that it must be organized into virtual “rows” and “columns”. Each “row” is separated from the next “row” by the main separator character (see below). Each “row” is divided into two “columns” by the sub separator character. The first “column” contains the key and the second “column” contains the associated value.

key – is the value you are looking for. If this value matches a value in the first “column” of the table, the value in the second column of that row will be returned. If there is no match, the default value will be returned.

mainseparator – is the character that separates each row.

subseparator – is the character that separates the columns within each row.

default – is the value that should be returned if there is no match.


This function uses a key to look up a value in a two dimensional text array (see Text Arrays). It is very similar to the lookup( function, but instead of looking up the data in a database, it looks it up in a text array.

If the main separator is a carriage return and the sub separator is a comma, you can easily see the two dimensional structure of the array.

global stateAbbreviations

Once you’ve set up an array like this, you can use it to look up values.

arraylookup(stateAbbreviations,"CO",cr(),",","-") ☞ Colorado
arraylookup(stateAbbreviations,"RI",cr(),",","-") ☞ Rhode Island
arraylookup(stateAbbreviations,"XT",cr(),",","Unknown") ☞ Unknown

In the final example, there is no state matching XT, so the default value, in this case Unknown, is returned. You can set the default to anything you want, including nothing.

Using carriage returns for the main separator makes the organization of the array easy to see, but you can pick any separator characters you wish. Here is another way you could set up the array, using | and = as the separators.

global stateAbbreviations
stateAbbreviations="AL=Alabama|AK=Alaska|AZ=Arizona|AR=Arkansas| ... |WI=Wisconsin|WY=Wyoming"

This will work fine as long as you use the same separators in the arraylookup( function.

arraylookup(stateAbbreviations,"OR","|","=","Unknown") ☞ Oregon

The order of the rows in the array is unimportant unless there are duplicate key values. In that case, the the value associated with the first key value in the array will be returned.

See Also


10.0No ChangeCarried over from Panorama 6.0