Partial match with VLOOKUP

[sanjdhar]

Hi there,

Is there a way to emulate Excel Partial match with VLOOKUP as outlined in this article:

https://exceljet.net/formula/partial-match-with-vlookup

I know the Join tool is the equivalent of vlookup, but I need to match on wildcard (*)

Thanks

[atcodedog05]

Hi @sanjdhar 

Find and replace is the equivalent of vlookup. It also does a partial match. Here is a resource on it.

https://community.alteryx.com/t5/Interactive-Lessons/VLookUps-with-Designer/ta-p/80201 

Hope this helps : )

[sanjdhar]

No it didn't really help.

The Left Join data comes from the tab JoinLeft (see attached). The lookup values come from the tab JoinRight. The key for the match is in the first column in each tab. 

In Excel vlookup (with FALSE option), I can pull the Acrobat Std / Pro as the mapped value since the vlookup matches the ACROBAT CC Acrobat CC * against the ACROBAT CC Acrobat CC Acrobat Std / Pro in the reference lookup.

What I desire is something like this:

key (segments + segments offering + app)	Scenario Fwk	Units	MappedValue
ACROBAT CC Acrobat CC *	Actuals 202101	12	Acrobat Std / Pro
ACROBAT CC Acrobat CC *	Actuals 202102	13	Acrobat Std / Pro
ACROBAT CC Acrobat CC *	Actuals 202103	19	Acrobat Std / Pro
ACROBAT CC Acrobat CC *	Actuals 202104	9	Acrobat Std / Pro
ACROBAT CC Acrobat CC *	Actuals 202105	7	Acrobat Std / Pro
ACROBAT CC Acrobat CC *	Actuals 202106	16	Acrobat Std / Pro
ACROBAT CC Acrobat CC *	Actuals 202107	27	Acrobat Std / Pro
ACROBAT CC Acrobat CC *	Actuals 202108	25	Acrobat Std / Pro
ACROBAT CC Acrobat CC *	Actuals 202109	20	Acrobat Std / Pro
ACROBAT CC Acrobat CC *	Actuals 202110	19	Acrobat Std / Pro
ACROBAT CC Acrobat CC *	Actuals 202111	23	Acrobat Std / Pro
ACROBAT CC Acrobat CC *	Actuals 202112	19	Acrobat Std / Pro
ACROBAT CC Acrobat CC *	Actuals 202113	26	Acrobat Std / Pro
ACROBAT CC Acrobat CC *	Actuals 202114	20	Acrobat Std / Pro
ACROBAT CC Acrobat CC *	Actuals 202115	16	Acrobat Std / Pro
ACROBAT CC Acrobat CC *	Actuals 202116	21	Acrobat Std / Pro
ACROBAT CC Acrobat CC *	Actuals 202117	17	Acrobat Std / Pro

[mst3k]

it sounds like you want to use a wildcard in your join, instead of a strict value, basically? 

a few ways you could do that - a fuzzy match might work, but i don't know how to use fuzzy match... another way would be to append the values together, then do a filter tool with "contains" to check the values.

this is an oversimplified example - i assumed your search term always ends with *, rather than allowing a wildcard in the middle. but you could play with it from there

[sanjdhar]

Yes, that is what I ended up in implementing, except that the append fields max out at 16. So I had to use an iterative macro to exhaust all the vlookup reference values given the 16 record append limitation. 

[mst3k · Accepted solution]

Sorry you built the macro already! It doesn't stop at 16, that's just the default. It's very easy to miss, because for me at least I have to scroll down in the Append tool configuration to see it, and it's below a bunch of white space. You change that to allow all appends.

Be careful because you will get X * Y records so don't use huge datasets