Fuzzy lookups [UDF]
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
SEARCHCHARS(lookup_value, tbl)
Arguments
lookup_value | Required. The value you want to lookup. |
[tbl] | Required. The range you want to search. |
Example
User defined function in cell C3:
Copy cell C3 and paste it to the cells below, as far as needed.
Excel vba
'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) 'Same character? 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) End If 'Next character Next i a = a - Len(cell) 'Save value if there are more matching characters than before If a > b Then b = a Value = str End If a = 0 Next cell 'Return value with the most matching characters SearchChars = Value End Function
How to add the user defined function to your workbook
- Press Alt-F11 to open visual basic editor
- Press with left mouse button on Module on the Insert menu
- Copy and paste the above user defined function
- Exit visual basic editor
This formula returns multiple values even if they are arranged differently or have minor misspellings compared to the lookup value.
This user defined function creates a unique distinct list of words and how many times they occur in the selected […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
This article demonstrates a user defined function that lists files in a ggiven folder and subfolders. A user defined function is […]
The image above demonstrates a user-defined function in cell range B6:D7 that allows you to search a folder and subfolders […]
Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]
This post describes how to split words in a cell range into a cell each using a custom function. I […]
This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]
This article demonstrates a User Defined Function (UDF) that counts unique distinct cell values based on a given cell color. […]
The SUBSTITUTE and REPLACE functions can only handle one string, the following User-Defined Function (UDF) allows you to substitute multiple […]
This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]
This blog post describes how to create permutations, repetition is NOT allowed. Permutations are items arranged in a given order meaning […]
AJ Serrano asks: I have a column where each rows contains different values and I wanted to obtain the duplicate […]
This article explains how to perform multiple lookups based on values in one cell with a delimiting character using a […]
This article describes a User Defined Function that lookups values in multiple cross reference tables based on two conditions. A […]
Question:I would like to reduce the number of open items by identifying positive and negative amounts that net to zero […]
Macros and custom functions are great, they can automate many tedious tasks. To have them available whenever you need them, […]
This article demonstrates formulas and a UDF that searches for values in a table based on concatenated values and returns […]
This blog post demonstrates a custom function (UDF) that creates permutations. Repetition is allowed. The custom function lets you specify the […]
This article describes how to find a sum from a range of numbers using a user defined function. Let´s see […]
In this vba tutorial I am going to show you how to return values from an udf, depending on where […]
2 Responses to “Fuzzy lookups [UDF]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Is is possible if the script find different values? I mean multiple occurences if find multiple matches?
Thanks
Rizkyu
[…] https://www.get-digital-help.com/2011/04/04/excel-udf-fuzzy-lookups/ […]