Lookup for unmatched values "Part C" - VBA

Download working file 👈👨‍💻

Here I am going to show you how we can get the lookup result for a lookup value where there is spelling difference in the lookup value and Table array. 

Robert Ilbrink helped me out here to create this unique and very powerful UDF

In the above example we can see that the Lookup value spelling is different from the one which is mentioned in the Table Array. In this situation we will create a User Defined function called "findmatch" in VBA which can help us identify the most appropriate match for this lookup value in the Table array.

For this copy the below code to Excel VB Window.

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

It has 2 elements:

 - Lookup value

 - Lookup Range

It will try to find out the most appropriate match of the Lookup value from the table array.

Please note - It may not be always accurate.

See below on how to use this UDF

Now we can use this UDF in our formulas to get the desired result. In this example we will use this along with VLOOKUP to get the State.

See below:

Download working file 👈👨‍💻

Thank You!!