Confused look up match function

%3CLINGO-SUB%20id%3D%22lingo-sub-1919590%22%20slang%3D%22en-US%22%3EConfused%20look%20up%20match%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1919590%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20look%20up%20for%20a%20similar%20value%20in%20Column%20G%20from%20Column%20A.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%3A%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20A%20has%20%22Retail%20and%20Distribution%22%20so%20I%20want%20to%20match%20it%20with%20Column%20G%20as%20%22Retail%22%20and%20return%20it%20like%20that.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20lists%20so%20declaring%20a%20string%20is%20i%20think%20will%20put%20a%20limit%20to%20a%20formula.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20tried%20some%20couple%20of%20things%20such%20as%20Search%20and%20other%20things%20but%20cant%20seem%20to%20find%20the%20correct%20one.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20may%20want%20to%20check%20the%20attached%20file%20for%20references.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1919590%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1919654%22%20slang%3D%22en-US%22%3ERe%3A%20Confused%20look%20up%20match%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1919654%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F876726%22%20target%3D%22_blank%22%3E%40Toryo0306%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1919694%22%20slang%3D%22en-US%22%3ERe%3A%20Confused%20look%20up%20match%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1919694%22%20slang%3D%22en-US%22%3EThanks%20for%20the%20answer!%20Im%20trying%20to%20match%20a%20set%20of%20strings%20to%20a%20list%20of%20strings.%3CBR%20%2F%3EThis%20is%20because%20there%20are%20some%20value%20in%20a%20cell%20that%20has%20different%20strings.%3CBR%20%2F%3E%3CBR%20%2F%3EExample%3A%20the%20result%20should%20be%20%22RETAIL%22%20but%20I%20have%20cells%20that%20has%20%22RETAIL%20AND%20WHOLESALE%22%2C%20%22RETAIL%20AND%20COMPUTERS%22.%3CBR%20%2F%3E%3CBR%20%2F%3EOther%20example%3A%20result%20should%20be%20%22Computers%20and%20Technology%22%2C%20but%20I%20have%20cells%20that%20has%20%22Computer%20manufacturing%22%20or%20%22Computers%20and%20infomation%22%3CBR%20%2F%3E%3CBR%20%2F%3EHope%20this%20helps.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1919698%22%20slang%3D%22en-US%22%3ERe%3A%20Confused%20look%20up%20match%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1919698%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F876726%22%20target%3D%22_blank%22%3E%40Toryo0306%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGot%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20strongly%20offer%20you%20to%20watch%20Ken%20Puls's%20video%20and%20use%20Left%20Out%20Join%20option%20in%20Power%20Query%20to%20solve%20this%20issue.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D7Xc1fUsk--c%26amp%3Bt%3D23s%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D7Xc1fUsk--c%26amp%3Bt%3D23s%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFrom%2054's%20minute%20to%20106's%20minute.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGood%20luck%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1919734%22%20slang%3D%22en-US%22%3ERe%3A%20Confused%20look%20up%20match%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1919734%22%20slang%3D%22en-US%22%3E%3CP%3Eoh%20wow%20Thanks!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi!

 

I want to look up for a similar value in Column G from Column A. 

 

For example: 

Column A has "Retail and Distribution" so I want to match it with Column G as "Retail" and return it like that. 

 

I have lists so declaring a string is i think will put a limit to a formula.

I have tried some couple of things such as Search and other things but cant seem to find the correct one. 

You may want to check the attached file for references. 

7 Replies

@Toryo0306 

 

I need a little bit more detailed explanation.

 

However, I made unique lists of two columns.

 

 

P.S. It seems to me that your request can be solved with Power Query Fuzzy Matching Option (After your explanation)

 

 

Thanks for the answer! Im trying to match a set of strings to a list of strings.
This is because there are some value in a cell that has different strings.

Example: the result should be "RETAIL" but I have cells that has "RETAIL AND WHOLESALE", "RETAIL AND COMPUTERS".

Other example: result should be "Computers and Technology", but I have cells that has "Computer manufacturing" or "Computers and infomation"

Hope this helps.

@Toryo0306 

 

Got it.

 

I strongly offer you to watch Ken Puls's video and use Left Out Join option in Power Query to solve this issue.

 

https://www.youtube.com/watch?v=7Xc1fUsk--c&t=23s

 

From 54's minute to 106's minute.

 

Good luck

 

SESSION OUTLINE:Joseph Yeates will start off the evening by going over some of the latest features in Power BI.Next, Ken Puls will present on different ways ...

oh wow Thanks!!!

@Toryo0306 

 

 

Please check attached file.

 

I think it should be answer to your question.

 

To get more correct answer change ratio seen on picture (Power Query Fuzzy Matching)

 

Ilgar_Zarbaliyev_0-1605915896131.png

 

 

It can be from 0 to 1. Default is 0.8

i like it more formula base since ill be dealing more of this everyday. Thanks for the answer and knowledge!

@Toryo0306 Straight-forward VLOOKUP will match 17 out of 67 unique industries in the long list. Standard Fuzzy matching in power query will find 19 matches. So, not much of a gain IMHO. Lowering the "fuzzy threshold" will slightly increase the number of matches, but with a risk of incorrect matches. 

 

Your real challenge lies into mapping the remaining 50 unique industry references from the long list that do not exactly match one from the shorter list. With reference to the video, you need a translation table telling Excel what to match with what. For example, "Consumer Electronics" could be Consumer Goods, Computers & Electronics or perhaps even Retail. Another example is "Hospitality, Travel & Leisure". Would you match it to Hospitality or Travel? You have to tell Excel. It can't be the other way around. Once you have done that, you can apply VLOOKUP (or XLOOKUP if supported in your Excel version) to all your 976 rows of data and attach the correct overall industry name to each of them.