Nov 20 2020 01:47 PM
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.
Nov 20 2020 02:27 PM - edited Nov 20 2020 02:32 PM
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)
Nov 20 2020 02:50 PM
Nov 20 2020 02:56 PM
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
Nov 20 2020 03:45 PM
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)
It can be from 0 to 1. Default is 0.8
Nov 20 2020 04:40 PM
Nov 20 2020 11:43 PM
@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.