Although Lookup is available via the Lookup Wizard in Map Designer, its use is limited because it isn't able to take macros for the input file. The deployment will be cleaner without the wizard because there will be one fewer RIFL file to deploy.
To use the lookup function, build an expression in one of the target fields.
Lookup(Fields("State"), MacroExpand("$(DATA_DIR)regions.txt"), ",")
This function reads a file under $(DATA_DIR)regions.txt which is delimited by a comma (','). The call uses the source field "State" as a key. Other parameters let you adjust the order by which column the key is identified (first column or second column).
This is the regions.txt file I used as a source for the Lookup function.
State,Region MD,Northeast NY,Northeast CT,Northeast DE,Northeast DC,Northeast CA,West CA,West AZ,West WA,West WA,West NC,South SC,South VA,South FL,South GA,South
The result is a MySQL table that loaded input data -- in this case sales associate contact info -- with the appropriate region given a U.S. state.
The Flat File Wizard is much less useful than the Incore Table Lookup Wizard. The code listing below is the generated product with comments stripped. Note the absolute path. I entered a macro in the wizard which allowed me to navigate to a directory, but was expanded when the code was generated.
''''''''''''''Generated by Flat File Lookup Wizard'''''''''''''''''''''
Function SalesRegion_Lookup(KeyValue, DefaultValue)
If DefaultValue <> "" then
SalesRegion_Lookup = Lookup(KeyValue, _
"C:\Users\Public\Documents\Data\regions.txt", ",", 0, DefaultValue)
SalesRegion_Lookup= Lookup(KeyValue, _
"C:\Users\Public\Documents\Data\regions.txt", ",", 0, "")