I have a module with say a Product dimension consisting of 3 Letter product codes, e.g. APP, ORG, PEA. I would like to find these items in a list where the Names in the List are 5 Letters e.g. APPUS, ORGUS, PEAUS. How am I able to lookup APP against the list of APPUS (first 3 letter) and return ÀPPUS?
I will give below my solution once again in steps based on the mock up you have shared.
1) First create another module and lets name it as "StagingModule" dimensioned by List 1.
2) Create two line items and lets name it as "LineItem1 & "LineItem2".
3) The format of LineItem1 will be List 1 formatted line item.
4) The format of lineItem2 will be List 2 formatted line item.
5) In LineItem1, the formula should be ITEM('List 1')
6) In LineItem2, the formula should be FINDITEM('List 2',left(name(item('List 1')),3)).
7) Now back to your original module showed in the mock where you want the result. You can put the formula in the product line item as 'StagingModule'.'LineItem1'[FIRSTNONBLANK:'StagingModule'.'LineItem2'].
See to that when you input the formula you put the brackets correctly otherwise you might get the formula invalid error. If any error, please do share the error screenshots and blueprint view of the module and formula you have put.