Hi trying to obtain the following: Search for both strings from Field1 (AB and 12345.0) in Field2 and return Field3 Field1AB 12345.0 Field2Field3CD blah blah blahAAB blah blah-12345.0B Desired output: Field1Field3 AB 12345.0B 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!

Vlookup multiple strings

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!

Workflow

Datasets

Developer Tools

Regex

Custom Tools

Common Use Cases

Fuzzy Match

Join

Preparation

Accepted answers

Elias_Nordlinder

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

Lookup.yxmd

All comments

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

Lookup.yxmd

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 : )

vlookup.yxmd

lmanda

@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

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

Lookup.yxmd

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!!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703