VLookup / Find Replace Tool with wildcards

I've got some Source data with 3 fields:

...and a lookup file with 3 corresponding fields that I want to match to bring in the 2 TEST_FLAG fields:

From the two I can easily create a key. The challenge is that whereas in the Source data all 3 fields are populated with real values, in the lookup we have wildcards * that represent any value.

For example in the lookup TAX_KEY value 431_*_* should join to Record 2 in source i.e. value 431_12345_123 or indeed any record that starts 431_

In Looksups the first part of the key will always have a real value, but the second and final parts may either be wild or real.

LOOKUPS_1A.xlsx

SAMPLE_FOR_WILD_LOOKUP.xlsx

Fuzzy Match

Accepted answers

Christina_H

I think the problem here is that @Shane76 is looking for the original 5 records with matched test flags. Try this one instead - it relies on the product code never being a wildcard for the initial join.

Wildcard match.yxmd

All comments

AngelosPachis

Hi @Shane76 ,

Assuming I understand the requirements correctly, the find and replace tool will allow you to look for a certain string at any part of another string, so that should help you overcome the wildcard issue.

I've transposed all 3 source data fields into a single column and looked for those values at any part of the Tax Key column.

Hope that's what you're after, let me know if that works.

Cheers,

Angelos

example.yxmd

Shane76

Hi Angelos thanks for the reply but I'm not really sure what you've done there. What I'm after is something like the below but as you can see if I use a Find Replace Tool I am only getting the exact match on Record 1 not a wildmatch on Record 2 for example:

Christina_H

Wildcard match.yxmd

Shane76

Thank you Christina. That gives the correct results. I'm going to go away and try to understand why now. THANX!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703