Search code examples
excelexcel-formulagoogle-sheetsgoogle-sheets-query

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")
Solution

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