Partial or fuzzy lookup

I’m trying to figure out to lookup values with “CONTAINS” statement and using multiple conditions.

Here are some examples:

Lookup table has the following values:

Col 1                            Return Val
CINCINNATI                         CIN
COLUMBUS, OH                        CL1
COLUMBUS, GA                        CLGA
DENVER                             DN

Main table has the following columns:

City                      State   
CINCINNATI                 OH
COLUMBUS                   GA
DENVER                     CA

The problem is that lookup table has optional state while it is always present in the main table, so I’m not sure how to perform correct lookup.

Thank you!

1 Like

In this case, I would do it as a 2-phase lookup. First with the stricter condition, and for remaining empty values - with the looser one.

See the example below.
2-phase-lookup.morph (4.5 KB)
Book1.xlsx (9.9 KB)

It can be possible to generalize the approach: do all the lookups corresponding to parts of the lookup condition, then use the “Rule” action as in the example to pick the correct lookup value.