-1

I am doing a survey on household appliances, visiting stores. I note down the full model number by hand in a notebook. Back home, I need to fill these in a spreadsheet. I have a partial list of the model numbers. I want to minimise my effort by entering only unique parts of the model number string.

  1. I'm using vlookup with * wildcard in excel for non-exact match. But problem is that it returns the first match, even though a better match is available.
  2. Also, I might miss out on a hyphen or "/" and I need some mechanism to correct for that.

Is there any solution for Excel, Libreoffice/Openoffice or Google Sheets?

DS R
  • 235
  • 2
  • 13
  • Any clues anyone? I need help urgently.. – DS R Aug 15 '17 at 13:01
  • Can you share the code and a sample with dummy data? It is easier to correct than to make up something new, not to mention it will show what you've done so far. – AntiDrondert Aug 15 '17 at 13:16
  • Sample models in database: RT28K3043S8/NL, RT28K3083S9/NL, GL-Q/282SGSM, GL-B/282SGSM; sample entry in cell: b282sg, formula in cell=VLOOKUP("*"&B2&"*",$A$2:$B$485,1,TRUE) ;; might return cell GL-Q/282SGSM, which is a lesser match than GL-B/282SGSM. I'd want it to be the latter, obviously. I want to save time by entering only the unique part of each product code string – DS R Aug 15 '17 at 13:33
  • Even it might sound easy, but finding closest match with strings is harder than it seems. Check [Getting the closest string match](https://stackoverflow.com/questions/5859561/getting-the-closest-string-match) – AntiDrondert Aug 15 '17 at 14:12
  • Thanks to all for the help. – DS R Aug 16 '17 at 05:06

1 Answers1

1

Assuming your model numbers are in column A and your search value is in B2, you can use this. It will handle the "/".

=query(A:A,"select A where lower(A) contains '"& lower(B2) &"'")
Ed Nelson
  • 10,015
  • 2
  • 27
  • 29