Formula Expression
XLOOKUP () or XLOOKUPBYNAME ()
Purpose
to apply a mapping rule table by searching any table in the database looking for a matching record based on up to 10 key values, and returns the value of the specified field from the lookup table if a match is found or a defined default value otherwise..
Benefits
use a table ID, instead of a table name as LOOKUPBYNAME() function does.
Use nAliasNo to avoid multiple openings of the same table.
Syntax
XLOOKUP (nAliasNo, nTableId, cReturnType, DefaultValue, ReturnField, Key1, Key2,...,Key10)
XLOOKUPBYNAME (nAliasNo, cTableName, cReturnType, DefaultValue, ReturnField, Key1, Key2,...,Key10)
nAliasNo: INT from 1 to 50.
Use same nAliasNo if using same lookup table and key fields.
nTableId: INT.
Object/Table ID
cReturnType: STR.
"A": Alpha.
"N": Numeric.
DefaultValue:
Default value returned if a matched record is not found.
ReturnField:
can either be a number or a name.
Key1,...,Key10:
Up to ten key values may be defined for the search for a matching record.
Examples
[COITYP] =SUBSTR(XLOOKUP(1,2,”A”,”XXX”,23,[Plan]),1,1)