Look up values based on 2 conditions. one of which is fuzzy match

[Anonymous]

Hello,

I have 2 tables:

Table 1 contains cost stations codes and cost stations names in SAP. The cost stations can be repeated if there are many sections underneath this cost station:

Cost station codes	Cost station names
5271	Factory 1 - Vegetable processing section
5271	Factory 1 - Meat processing section
5831	Factory 3 - Meat packaging

Table 2 contains cost station codes, cost stations names and index in a different in-house system. The cost station codes are the same with Table 1 but the cost station names are diffrent. The cost station names are also not following any rules. Index is unique.

Cost station codes	Cost station names	Index
5271	Vegetable processing unit/Factory 1	1
5271	Meat processing unit - factory 1	2
5831	Factory 3 - Meat package	3

I want to have another column of Index added to Table 1.

Edit: I want to get the Index lookup that takes into consideration Cost Station Names because the Cost Station Codes are not unique for both tables. If Cost Station Names from Table 1 is similar (fuzzy match) to Cost Station Names from Table 2, check if the cost station codes matches. If they do then get index of the match, else look up only Cost Station Code

Edit: I want that the Cost Station Code must exactly match and the Names can "kind of" match.

How can I achieve this? 

Thank you in advance 🙂 

[Syndicate_Admin]

Hello - this gets the job done.  

You can merge the tables using fuzzy match.  In order to match on Cost Station Code as one of the criteria, it must be formatted as text.  Then you can match on both columns and select the additional fuzzy match options.  I have included a sample pbix.

Table1 final result:

Merge Tables Using Multiple Columns and Fuzzy Matching.pbix  

[jennratten]

Hello - this gets the job done.  

You can merge the tables using fuzzy match.  In order to match on Cost Station Code as one of the criteria, it must be formatted as text.  Then you can match on both columns and select the additional fuzzy match options.  I have included a sample pbix.

Table1 final result:

Merge Tables Using Multiple Columns and Fuzzy Matching.pbix  

[Anonymous]

Hello,

Thank you very much for your reply. I have tried this way but turns out a lot of the fuzzy matches also matches with the wrong codes, which must be exact matches. I have tried to increase/decrease the threshold but it also does not result in very correct matches.