In this post I will describe a basic user defined function with better search functionality than the array formula in this post: Fuzzy vlookup.
The user defined function searches for a value with as many characters matching as possible. It is as simple as that.
I have added some serious misspellings randomly in List 2. 98 out of 100 names are found.
User defined Function Syntax
Required. The value you want to lookup.
Required. The range you want to search.
User defined function in cell C3:
Copy cell C3 and paste it to the cells below, as far as needed.
'Name function and arguments
Function SearchChars(lookup_value As String, tbl_array As Range) As String
'Declare variables and types
Dim i As Integer, str As String, Value As String
Dim a As Integer, b As Integer, cell As Variant
'Iterste through each cell
For Each cell In tbl_array
'Save cell value to variable
str = cell
'Iterate through characters
For i = 1 To Len(lookup_value)
If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then
'Add 1 to number in array
a = a + 1
'Remove evaluated character from cell and contine with remaning characters
cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid(cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999)
a = a - Len(cell)
'Save value if there are more matching characters than before
If a > b Then
b = a
Value = str
a = 0
'Return value with the most matching characters
SearchChars = Value
How to add the user defined function to your workbook