1

I am trying to compare Text.

-Input are short statements about a dish (i.e. Noodles with Vegetables /Spagettti and steak etc) typed by serval hundred people

-Control are the names of the actual dishes (i.e Homemade noodles served with vegetables, Spaghetti with cordon blue)

For each word (even if misspelled) a point should be awarded if it is mentioned within the correct dish in the control column.

Column of Input dish can but does not always correspond with the dish in the control column. (f. e. Input A1 is cordon Blue, A2 Steak / control in B1 is Homemade noodles served with vegetables, in B2 Spaghetti with cordon blue)

I tried the VLOOKUP function with an approximate match. But the match is to approximate, since a single identical letter within a word results in a match. I cant have Michael Jordan count as a cordon blue…

Also tried single word comparison with VLOOKUP what resulted in same problem as before.

VLOOKUP with exact match does not work for misspelled words. Same for EXACT.

Whatever does the job must also work when I add multiple IF / COUNTIF and SUM functions later to the formula.

Thanks.

HvG
  • 43
  • 8
  • 1
    Besides @kobyDouek's good answer below, if you feel like getting into the nuts and bolts, you might consider implementing a [levenshtein distance algorithm](http://stackoverflow.com/questions/4243036/levenshtein-distance-in-excel) in VBA and running your searches through that. You might also find Soundex/Soundex2 algorithm useful here as well. – JNevill Mar 06 '17 at 14:29
  • 1
    And another possibility is to control using Data Validation what is entered as a dish using a pre-set list... – Solar Mike Mar 06 '17 at 14:42
  • @kobyDouek I forgot to mention I'm on OSX. Cannot find a compatible download for Fuzzy Lookups. VLOOKUP's 4th argument is the one I've been trying. Completely different words get a match just for having two similar letters. ( i. e. crodon blue and Michael Jordan) I believe the MATCH function uses the same arguments… – HvG Mar 06 '17 at 14:55
  • I don't think that's how the VLOOKUP inexact match works--it assumes your lookup list is in alphabetical order and picks the last value that is less than the one you're looking for. There is a great explanation in the link that @kobyDouek gave in his answer. – vknowles Mar 06 '17 at 15:21

1 Answers1

3

You can use VLOOKUP's 4th argument: Excel describes the TRUE value in the 4th argument (range_lookup) value as “Approximate Match”.

If the Approximate Match does not suffice, and you need a more "fuzzy" lookup, Microsoft offers a free add-in that enables Excel to perform fuzzy lookups. It is called “Fuzzy Lookup Add-In for Excel” and is available at the time of this post at the link below:

http://www.microsoft.com/en-us/download/details.aspx?id=15011

Once installed, this add-in performs fuzzy lookups. It does not change the behavior of any of the built-in lookup functions. It does not enable your VLOOKUP functions to perform fuzzy lookups. It is an add-in which basically processes two lists and computes the probability of a match.

Source: http://www.excel-university.com/perform-approximate-match-and-fuzzy-lookup-in-excel/

Hope this helps.

Koby Douek
  • 16,156
  • 19
  • 74
  • 103
  • Why doesnt it ? – Koby Douek Mar 08 '17 at 11:13
  • because it is to fuzzy.. i cant have two semantically different words result in a match just because they have some same letters – HvG Mar 10 '17 at 07:07
  • It works but the following is extremely important (and may explain @HvG problems) : the list you are V-looking on with the 4th argument set to true must be SORTED (it's explained in the link you gave) – Cyril Duchon-Doris Oct 01 '19 at 09:20