Re: Is lookup is used for only two fields of table ?
If there are several possible values in the lookup table, the lookup function will just take the first one found.If you do expect several values for each of the input values, you should not use a lookup function to solve the problem.
in above, Lookup() function will return the value of the field ProductCategory (first parameter) in the table ProductTable (fourth parameter) from the record where the field ProductID (second parameter) has the same value as the field ProductID in the order data table (third parameter).
if you want to get multiple values, then use multiple time as below
Lookup ('ProductCategory', 'ProductID', ProductID, 'ProductTable') as Lookup1,