Table returns the value from a lookup table given a certain column and row value.  See Lookup Table Setup for information on creating Lookup Tables.

SYNTAX:

Table(TableName,ColValue,RowValue,MatchType,GetOutside,Optional Type)

TableName: The lookup table name.  To setup a lookup table see Lookup Table Setup.

ColValue: An expression (numeric value or formula) that specifies the column lookup value.

RowValue: An expression (numeric value, or formula) that specifies the row lookup value.

MatchType Specifies what to do if an exact match is not found.

1 - Average closest values

2 - Use closet value less than Lookup value

3 Use closest value greater than Lookup value

4 Use exact match only

5 Straight line Interpolation of values

GetOutside: Specifies the column or row to use if the lookup values are outside the range of the table.

0 Return blank if outside range

1 Use closest row or column to the lookup value

Type:  Optional. Sets what datatype to return.  1 returns a number (default), 2 to return a string (Text).

NOTES:

In order to locate text values for columns and rows, text must be in quotes

EXAMPLES:

TABLE(“CT”,A1,A2,5,0)

Returns the "Straight Line Interpolation of Values" located in the  "CT" table for the column value in cell A1 and the row value in cell A2

STR(Table("Exempt Status","OPS Systems", "Rio Rancho", 4,1,2))

Returns the tax exempt status of "Exempt" from the table"Exempt Status" for the "Company" (Column) in a particular "City" ( Row). Column and row text values must be in quotes.

The Table() function can be used in CT calculations; to do so, OPS has provided the necessary lookup tables here.