This function lets you find similar strings from a range without having to do an exact search.
The formula looks like this: =FuzzyFind(A1,B$1:B$20) assuming the string you are performing a search for is in A1 and your reference or options table is B1:B20
The code is here:
Function FuzzyFind(lookup_value As String, tbl_array As Range) As String Dim i As Integer, str As String, Value As String Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid(cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell FuzzyFind = Value End Function
The results from this function are hit and miss. Can anyone improve the intelligence of this algorithm?
Thank you :)