use of Lookup_Wild and regular expressions

Question

has anyone used Lookup_wild and could share an example of a successful lookup_wild call?
essentially like to use RE in the search because exact match only returns a subset of desired results.
I have a source_column that is contained somewhere within the matching_column, looking to retrieve 4 additional columns from the lookup_table
#ApptioforAll

Accepted Answer

Hi @Arthur Parkos,
Below is an example of how you could use Lookup_Wild().
But first, let me just mention that using a function that "looks into" the values of the text is more expensive in terms of performance than a function that only checks if a=b or a!=b. So, if your tables are large, see if you can simplify the task before using Lookup_Wild, e.g. referring to what @Debbie Hagen mentioned about know where in the string the value may appear.

Lookup_Wild() example:

Source Table:
Source Column:
xaaa
aaax
xaaax
aaa
xbbbx
ccc

Lookup Table:
Matching Column, Replacement Column
aaa,A
bbb,B

1. Go to Project Settings and enable this setting if the box isn't already checked:

2. In the Lookup Table, create a new formula:
Matching Column Regex = ".*"&Matching Column&".*"

3. In the Source Table, create the Lookup_Wild() formula:
Replacement Column = Lookup_Wild(Source Column,Lookup Table,Matching Column Regex,Replacement Column)

The result should be:

Source Table:
Source Column, Replacement Column
xaaa, A
aaax, A
xaaax, A
aaa, A
xbbbx, B
ccc, {null}

Hope this works for you, let me know if you have any further questions on this.

Thanks,
Guy

#ApptioforAll

Apptio for All