MatchThis function
This function works as if you were doing multiple lookup statements in one formula. The example I’m going to use is if you wanted to look at your credit card statement and from the description determine what vendor it is.
For this function to work I need to create a named range of all the values (e.g. possible vendors) I want to cycle through to compare the string (e.g. credit card data) against. The named range needs to be called LookupList. Below you will see the LookupList I created. (For more on named ranges, see this post)
I’ve added a header but that is not necessary. As long as the list is a named range called LookupList. The adjacent column is the value that will be returned if the value in the LookupList is found. You need to ensure this column is also filled in or else the result of the formula will be blank, regardless if there is a match.
When I run the custom function, the function will cycle through the LookupList from top to bottom to see if one of those values is in the cell I am using the formula on and if so, return the related result. For that reason, the LookupList also needs to be in descending order, to avoid a premature match (e.g. finding Store A before the function finds Store ABC)
Column A is an example of data from a credit card or other source that may have various characters before and after what you are looking for. You could use the MID function to extract that data but that will only work if that data is consistently arranged the same way. It might be, but using this function it won’t matter and it will just look if any of the values in the LookupList are contained in the string, regardless any other characters before and after.
Column B is the MatchThis function. And since the LookupList is already defined the only argument is the data that you want to look at, which in this case is column A. In column B2 the formula is simply =matchthis(A2). Because it matches Store A, it returns the value A (from the results column).
Below is the code for this function:
————————————————————————————————————————————–
Function MatchThis(matchcell As Range)
Application.Volatile
Application.Calculate
Dim LookupList As Range
Dim c As Range
‘Identify the range of cells you want to compare against. The lookuplist is what will be compared against and the column to the right of it will be the output
Set LookupList = Range(“LookupList”)
‘Go through each of the cells looking for the criteria in cell c, and if it matches, pull the value from the next column
For Each c In LookupList
If InStr(1, matchcell, c, vbTextCompare) > 0 Then
MatchThis = c.Offset(0, 1)
Exit Function
End If
Next c
End Function
————————————————————————————————————————————–
Add a Comment
You must be logged in to post a comment