4

I'm trying to fuzzy match two columns in google sheets, i've tried numerous formulas but I think it's going to come down to a script to help out.

I have a column with product ID's e.g.

  • E20067

and then I have another sheet with another column which has image url's relating to this product code such as

What I'm wanting to do is "fuzzy" match both of these columns for their product ID, and then create a new column for each match. So it would have the product ID then on the same row in multiple columns each product image URL - like the image below:

enter image description here

Is there a way to do this in google sheets using a script or a formula?

JMKelley
  • 599
  • 2
  • 17
  • 36

3 Answers3

2

In Google sheets there are a few powerful 'regex' formulas.

Suppose, you have ID list in column A, and URL list in column B

enter image description here

Then use formula:

=REGEXEXTRACT(B1,JOIN("|",$A$1:$A$3))

It will match one of ID's. Drag the formula down to see the result as in picture above.

See more info here

Max Makhrov
  • 17,309
  • 5
  • 55
  • 81
  • Thanks for the answer - but if each product ID has multiple images e.g. 5x images? – JMKelley Nov 07 '16 at 15:52
  • This works great to get a static column between data sets - but it's stopping due to it being over 50,000 characters? – JMKelley Nov 07 '16 at 17:04
2

Old thread but, in case you find yourself here, search for my Google Sheets add-on called Flookup. It should do exactly what you want.

For this case, you can use this function:

Flookup (lookupValue, tableArray, lookupCol, indexNum, threshold, [rank], [range])

The parameter details are:

  1. lookupValue: the value you're looking up
  2. tableArray: the table you want to search
  3. lookupCol: the column you want to search
  4. indexNum: the column you want data to be returned from
  5. threshold: the percentage similarity below which data shouldn't be returned
  6. rank: the nth best match (i.e. if the first one isn't to your liking)
  7. range: choose to return the percentage similarity or row number for each match

Please note that, whereas the OP appears to want the whole list of possible matches, Flookup will only return one result at a time.

Flookup can now return a list of all possible matches through its LRM mode.

  • 1
    Flookup library works great for me. Also includes formulas to get multiple results. Find it here: https://chrome.google.com/webstore/detail/flookup/ejcjhjbhdleeagbfpnkhaljhnoollhig – mozboz Aug 05 '19 at 15:29
  • 1
    Thanks for the support @mozboz and thank you for adding the link to Flookup. I will update my post to include the link to the website as well –  Aug 06 '19 at 05:41
1

Try the following. I am assuming the product codes are in Sheet1 and the URLs are in Sheet2. Both in column A:

=iferror(transpose(FILTER(Sheet2!$A$2:$A,Search("*"& A2 &"*",Sheet2!$A$2:$A))))

Copy down.

If you want to show the image instead of the url try:

=arrayformula(image(iferror(transpose(FILTER(Sheet2!$A$2:$A,Search("*"& A2 &"*",Sheet2!$A$2:$A))))))
Giacomo1968
  • 25,759
  • 11
  • 71
  • 103
Ed Nelson
  • 10,015
  • 2
  • 27
  • 29