Formula needed to find best match

Please see the attached sheet.

I want a formula to find the best (closest) match within a list and return and item code.

Been searching all over the internet and haven't had any luck.

It probably has to be a pretty complex formula for example i want to return "COKE DIET FREE" if the string is "Caffeine Free Diet Coke" or "Caffeine Free Coke Diet".

I hope I explained it well enough....

The worksheet kind of helps..

Thanks in advance.

You could use a User Defined Function for this.

Below is adapted from a solution to another post but based on your examples it should work ok... (apes your expected results)

Please Login or Register  to view this content.

The above, stored in a standard module in VBE, is invoked from a cell along the lines of other normal formulae - eg:

Please Login or Register  to view this content.

note: the code assumes (per sample) that codes are listed in order of length - ie Diet Pepsi after Pepsi, Diet Coke Free after Diet Coke and Diet Coke after Coke etc