CODEWITHSUNDEEP
excelexcel-formulagoogle-sheetsgoogle-sheets-query
Captain Kirk
Captain Kirk

Reputation: 1196

In Google Sheets or Excel, how do I search for values that are similar?

What I'd like to do is the following:

I have one file with 3 sheets (tab1, tab2, and tab3). In each, I have the column name "Company Name" and in tab1 I have two specific columns named 'tab2' and 'tab3' that represent the sheets tab2 and tab3. What I'd like to do is query the sheets tab2 and tab3 to see if there are company names that either match 100% or are similar to what's listed in the 'Company Name' column in tab1.

Example:

Tab1

Company Name

Great Shoes

Tab2

Company Name

Great

Tab3

Company Name

Greatness Shoes Inc

So in the above-mentioned scenario, I'd like to then input into the columns tab2 and tab3 in the sheet tab1 whether or not there was a partial match with a yes or no.

What is the best formula to do this in Excel or Google Sheets? I tried it in Excel but got as far as finding out partial matches with very low accuracy using this formula: 

=IF(ISNA(VLOOKUP(B2 "*",'tab2'!$A$2:$A$884,1,FALSE)), "No", "Yes")

Upvotes: 0

Views: 2714

Answers (2)

Max Makhrov
Max Makhrov

Reputation: 18717

has regex functions.

You may try:

  1. combine all the data in a single sheet (column A) to simplify the comparison
  2. use the formula:

=TEXTJOIN(";",1,FILTER(A:A,REGEXMATCH(A:A,A1) + REGEXMATCH(A1,A:A),A:A<>A1))

enter image description here

Notes:

  • The formula does not give a 100% match, you still need a human to look for misspellings.
  • The formula will give all found matches divided by semicolon
  • if no matches are found, the cell will be left blank

  • you may manually add some keywords to your list in order to combine similar company names: like a word "Great", which matches all companies with this word inside.

  • paste it in B1 and copy down.

Upvotes: 1

N.O.Davis
N.O.Davis

Reputation: 513

You could use a combination of ARRAYFORMULA, ISNUMBER, and SEARCH. Using the "great shoes" example from Max, if your data is in column, A in B1 you could put:

=ARRAYFORMULA(IF(ISNUMBER(SEARCH("Great",A:A))=TRUE,A:A,""))

So that searches column A for the string "Great." If it is found =TRUE then whatever is in that cell is returned. If nothing is found ,FALSE, then a blank is returned "". There would not be any need to "copy down." You could modify that "if true" and "if false" statements to modify data as you need. In place of the "Great" string, you could put a cell reference.

You could also use QUERY in a manner similar to ISNUMBER(SEARCH. In B1 you could put =QUERY(A:A,"Select A where A contains 'Great'",0)

You could also use INDEX and MATCH. If your DATA is in column A in B1 you could put something like =INDEX($A1:$A3,MATCH("Great",$A1:$A3,FALSE)). You would have to "copy down" the formula for each row. If you want to prevent the #N/A error from showing, wrap that all in an IFERROR formula. =IFERROR(INDEX($A1:$A3,MATCH("Great",$A1:$A3,FALSE)),"").

Upvotes: 1

Related Questions