In this article, we will try to get position of first partial match in a range.
As we know that MATCH function returns the index or position of first match in a range. Hence, it is obvious to use this function to get the position of first match in a range. Since, it supports the wildcard operators, we can also use MATCH to do partial matches.
Once we get the position of first match position, we can do various things. Like retrieving that value or adjacent or non adjacent value using INDEX function,, or making a dynamic function. It depends on your need and creativity.
Generic Formula for First Found Partial MATCH
For hardcode string:
For cell reference:
Str: it is the text or string that you want to partially match in range. It can be any thing, a cell or a hardcoded string.
Range: it is the range in which you will look for str.
0: It is the parameter for exact match. You can use FALSE too.
Note that we used to * (astrisks) in front and end of string str. This is the wildcard operator for matching any value having any string before and after it..
Let’s see an example.
Here I have a record of Win, Loss, and Tie. We want to get first position of Win, Loss, and Tie.
So, if I hard code then formula for finding first position of partial match in each cell will be:
How it works
It simply uses the functionality of excel to do partial matches. MATCH looks for any string that contains the provided string between * (asterisks) and returns the first found position.
Find Position of First Partial Match in Range using FirstPartMatch VBA Function
If you copy below vba code in vba module in excel, you can use this function to get first position of partial match. For above example, just write this formula:
You’ll get the position of first match. Like this.
How It Works
First argument is the string you want to search for partial match.
The second argument is the range in which you want to get position of your string.
To use this formula for partial matching, copy below code in VBA module.
Function FirstPartMatch(str As String, rng As Range) Dim tmp, position As Long position = 0 tmp = 0 For Each cll In rng tmp = tmp + 1 If InStr(1, LCase(cll.Value2), LCase(str)) > 0 Then position = tmp Exit For End If Next cll If position Then FirstPartMatch = position Else FirstPartMatch = "#NA" End If End Function
Case Sensitive Partial Match for Position
Above user defined function for finding first partial match will not be case sensitive. If you want it to be case sensitive, remove Lcase function from line 7.
Then the code for case sensitive will be:
Function FirstPartMatchCASE(str As String, rng As Range) Dim tmp, position As Long position = 0 tmp = 0 For Each cll In rng tmp = tmp + 1 If InStr(1, cll.Value2, str) > 0 Then position = tmp Exit For End If Next cll If position Then FirstPartMatchCASE = position Else FirstPartMatchCASE = "#NA" End If End Function
You can see that “won” and “tie” in this case are not found. Since there is no lower case won or tie.
So yeah guys, these are the ways to find the position of first partial match in excel. I told you you can find frist partial match position using predefined MATCH function and using user defined match function. If you have any other ideas or question, do share with us here in the comments section below.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.