Author: Oscar Cronquist Article last updated on February 16, 2018

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:

=SearchChars(lookup_value, tbl) + ENTER


User defined function in cell C3:

=SearchChars(B3, $E$3:$E$101) + ENTER

Copy cell C3 and paste it to the cells below, as far as needed.

Excel vba:

Option Explicit

Function SearchChars(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
SearchChars = Value
End Function

How to use user defined function in excel

  1. Press Alt-F11 to open visual basic editor
  2. Click Module on the Insert menu
  3. Copy and paste the above user defined function
  4. Exit visual basic editor

Download excel example file

(Excel 97-2003  Workbook *.xls)