Confused look up match function

Question

Hi!&nbsp;I want to look up for a similar value in Column G from Column A.&nbsp;&nbsp;For example:&nbsp;Column A has "Retail and Distribution" so I want to match it with Column G as "Retail" and return it like that.&nbsp;&nbsp;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.&nbsp;You may want to check the attached file for references.&nbsp;

riny_van_eekelen · Answer

Toryo0306&nbsp;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.&nbsp;&nbsp;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 &amp; Electronics or perhaps even Retail. Another example is "Hospitality, Travel &amp; 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.

ilgar_zarbaliyev · Answer

Toryo0306&nbsp;&nbsp;I need a little bit more detailed explanation.&nbsp;However, I made unique lists of two columns.&nbsp;&nbsp;P.S. It seems to me that your request can be solved with Power Query Fuzzy Matching Option (After your explanation)&nbsp;&nbsp;

toryo0306 · Answer

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. 🙂

ilgar_zarbaliyev · Answer

Toryo0306&nbsp;&nbsp;Got it.&nbsp;I strongly offer you to watch Ken Puls's video and use Left Out Join option in Power Query to solve this issue.&nbsp;https://www.youtube.com/watch?v=7Xc1fUsk--c&amp;t=23s&nbsp;From 54's minute to 106's minute.&nbsp;Good luck&nbsp;

toryo0306 · Answer

oh wow Thanks!!!

Forum Discussion

Confused look up match function

7 Replies

Resources