Vensim Help

Returns Lookup x,y pairs from Microsoft Excel for a Lookup variable or a vector of Lookup variables.  

Restrictions: Must appear directly following the left parenthesis ( that indicates the beginning of a Lookup definition.  

When a model is checked, GET XLS LOOKUPS queries Microsoft Excel for the values.  If Microsoft Excel  is not running Vensim will attempt to start it.  If Microsoft Excel does not have the specified file open Vensim will try to get Microsoft Excel to open it.  If Vensim cannot get values from Microsoft Excel it reports an error.  The values are also queried each time a simulation is made and a warning is given if the values are not obtainable.  If Vensim has trouble getting values with this function you should try opening the named file in Microsoft Excel first.

All of the arguments to GET XLS LOOKUPS must be either literals (enclosed in single quotes ') or String Variables. 'file' names a file with complete extension to read from (use '?Tag' to indirectly reference a file and resolve the reference in Model>Settings>XLS Files).  This can be an Excel file or any other type of file Microsoft Excel is capable of opening.  If no directory is specified (best practice) Vensim will append directory information for the current model. 'tab' names the tab that contains the Data.  This function will not work with older versions of Excel that do not support tabs. 'x row or col'  is either the number of the row containing x values (x running across) or the letter of the column containing x values (x running down).  Note that the spreadsheet file must contain values for x. 'cell'  names the cell that the first y value is on.  Vensim will read successive values across or down depending on the 'x row or col' argument.  Vensim will also read additional subscript elements in the other direction.

Note that you can substitute named Excel ranges for 'x row or col' and 'cell'.  To do this just create the named ranges in Excel and use those names in place of the Letter/Number designators.

NOTE Vensim will continue to read the x row or column to the end of the spreadsheet, ignoring any blank or nonnumeric cells.  If you want to have it read only a specific range use a named range.

 

Units: GET XLS LOOKUPS is not part of units checking.  Specify units for the left hand side variable.

Example

regular lookup((0,0),(1,1))

excel lookup(GET XLS LOOKUPS('test.xls','Sheet2','1','B2')) ~~|

subbed excel lookup[shape](GET XLS LOOKUPS('test.xls', 'Sheet2','1' , 'B2')) ~~|

 

The contents of the spreadsheet are shown below.  Note that the variable names do not need to match and that the order of subscripts determines how the data in the spreadsheet are interpreted.

NOTE GET XLS LOOKUPS is the same as GET DIRECT LOOKUPS on the Macintosh.

Availability:  Not PLE.

See also: GET DIRECT LOOKUPS, GET XLS DATA.

Sample model: GET XLS LOOKUPS.mdl  and GET XLS LOOKUPS[ss].mdl in FunctionExamples