Lookup table with Wildcards

[lipster26]

Hi,

I have two tables. One is a mapping table and the other one has raw General ledger data. I need to apply the mapping table rules to the GL data. The common columns in both tables would be Unit and Accounts, however the issue I'm having is that some Units in the Mapping table have Wildcards * and some are the full Unit Numbers. So I'm not sure how I can convert the Units in the GL data to a Wildcard format to match the Mapping table? I'm trying to lookup the Unit/Account from the GL data in the Mapping table and then add the Temp Diff Code from the Mapping table into a column in the GL Data table. Any help would be appreciated. Thanks.

Mapping Table

GL Data

[tsturge · Accepted solution]

I would first join the tables together "as is" bc I assume you have data from both sources that will join with no data change required.  Then take your outer lefts and use a formula tool to adjust the Unit Code into a wild card format.  Here is a rough idea of the process.