Vlookup multiple strings

[lmanda]

Hi trying to obtain the following:

Search for both strings from Field1 (AB and 12345.0) in Field2 and return Field3

Field1

AB 12345.0

Field2	Field3
CD blah blah blah	A
AB blah blah-12345.0	B

Desired output:

Field1	Field3
AB 12345.0	B

Previously in excel I would do something using wildcards like:

VLOOKUP("*"&"AB"&"*"&"12345.0"&"*",'Sheet1'!B2:F100,5,FALSE)

not sure how to achieve the same in alteryx though...any suggestions appreciated, thanks!

[Elias_Nordlinder]

Hello @lmanda,

You can perform similar function as the Vlookup in Excel in Alteryx with a combination

of a Formula Tool (RegEx_Replace) and a Join Tool.
This assumes the pattern that you used for the Vlookup function.

I have attached a workflow and the picture below:

1. Regex_Replace -> Looks for a specific pattern, in this case any one word

first, then anything after the hyphen.

2. Joins this new field with field 1

3. Only keeps field 1 and field 3 as you specified in your formulation.

Let me know if this answer your question or if you would need any more/more general workflow 🙂

//Regards

Elias

[atcodedog05]

Hi @lmanda 

Here is an exact replica of your vlookup.

Workflow:

1. Using regex to match the exact pattern and create lookup value

2. Using find and replace to do vlookup. https://community.alteryx.com/t5/Interactive-Lessons/VLookUps-with-Designer/ta-p/80201 

Hope this helps : )

[lmanda]

Hi

@atcodedog05and @Elias_Nordlinder 

thank you for your responses, however apologies I'm still unable to get the desired output for my data.

Let me try to illustrate my initial problem better:

Co. will always appear first in line description, but agreement can be anywhere and there is no consistent position/pattern for that.

I want to search for if line description begins with Co. AND contains agreement anywhere in it, then return Co., agreement, and account

Dataset 1

Co.	agreement
AB	12345.0
CD	12345.0

Dataset 2

Line description	Account
AB blah blah12345.0	B
CD blah 12345.0- blah	C

Desired output:

Co.	Agreement	Account
AB	12345.0	B
CD	12345.0	C

thank you!

[Elias_Nordlinder · Accepted solution]

Hello @lmanda ,

Okay, that changes it a little bit.

You can change your RegEx to something like this instead.

1. Change first part to [A-Z]{2}

-> Any two Capital letters, as we cannot be specific on which letters

2. Then create a Combination field of the two string of the first dataset.

-> As we want to see if the combination "AB 12345" etc. matches, not AB or 12345 separate.

3. Then use the Find Replace tool on the Combo field and Lookup Field.

4. Finally, use a select tool to only keep wanted columns in the end.

I used @atcodedog05 method with Find Replace instead of the join method I used earlier as this better replicates vlookup in Excel like he mentioned in his comment 🙂 

//Regards
Elias

[lmanda]

@Elias_NordlinderThank you so much! I was able to achieve the outcome I need after some small tweaks to the Regex formula,

Here is the tweak in case anyone else needs it:

Regex_Replace([Field2], "([\w]{2})*.*([\d]{8}.\d).*","$1 $2")

([\w]{2}) <-- for any 2 characters

([\d]{8}.\d) <-- for any 8 digit number rounded to 1 decimal

Thank you so much again, really appreciate it!!