Using Google sheets to tally data sets

Question

I have tried many formulas but i am still not able to get what i want. I need help to write an APP SCRIPT code for it. The problem is that I have to match two data sets and return the value of the adjacent cell. I want the sheet to pick a value from first cell of first row from a sheet and match it to entire cells of a row from other sheet (in the same workbook) and then paste the value which was being matched, infront of the cell which matches it. Now the problem is that my data sets are not equal so i can not use vlookup, i want to match and how much percentage it is matching. So highest percentage should be considered as a match. Kindly visit this link for an example in google sheet. [https://docs.google.com/spreadsheets/d/1u_-64UvpirL2JHpgA--GDa263wVb2idIhIYZlFnX2xQ/edit?usp=sharing]

Answer 1

There are a variety of ways to do this sort of partial matching, depending on the real data and how sophisticated you need to match logic to be.

Let's start with the simplest solution first. Did you know you can use wildcards in VLOOKUP? See Vlookup in Google Sheets using wildcards for partial matches.

So for your example data, add a column C to "Set 1" with the formula:

=VLOOKUP("*" & A2 & "*",'Set 2'!A1:A5,1,FALSE)

Obviously, this method fails if "Baseball bat" was supposed the be results for "Ball" instead of "Ballroom". VLOOKUP will simply return the first result that matches. This method also ignores case sensitivity. Finally, this method only works for appending data to set 1 from set 2, not the other way around. Without knowing more about the actual dataset, it's hard to give a solid solution.