CODEWITHSUNDEEP
excelmatchformula
Sean
Sean

Reputation: 1313

Excel SUMPRODUCT Substring Match

I am using SUMPRODUCT to match a cell equaling one of many things. Using the below formula, I am attempting to match the value 2147 (A single value from a column of many values) to the below variations of the number 2147:

=SUMPRODUCT( -- ("2147"=Table6[data])) > 0

 Table6[data]   Return Value
1 2147          TRUE
2 2147, 500     FALSE
3 2146-2148     FALSE
4 21475         FALSE

The first TRUE and last FALSE values are as expected (success), but I need the middle two to match TRUE (Identify the 2147 next to the , 500 and between the range 2146-2148.

Upvotes: 0

Views: 663

Answers (2)

SJR
SJR

Reputation: 23081

This uses a custom function. If you use it, I suggest you give it a more meaningful name.

So you'd put this formula in B1 and copy down

=Match2(2147,A1)

In outline, the function checks if a hyphen exists (using Split) and if so, checks the desired value against the lower and upper limits.

If not, using Split again, we split by commas, and if any element of the resultant array equals our desired value we return TRUE.

Function Match2(d As Double, r As Range) As Boolean

Dim v As Variant, i As Long

v = Split(r, "-")
If UBound(v) = 1 Then
    If Val(v(0)) <= d And Val(v(1)) >= d Then
        Match2 = True
    Else
        Match2 = False
    End If
    'we could shorten the five lines above to
    'Match2 = (Val(v(0)) <= d And Val(v(1)) >= d)
Else
    v = Split(r, ",")
    For i = LBound(v) To UBound(v)
        If Val(v(i)) = d Then
            Match2 = True
            Exit Function
        End If
    Next i
    Match2 = False
End If

End Function

enter image description here

Upvotes: 3

Scott Craner
Scott Craner

Reputation: 152660

Just for an an FYI, this is the formula:

=SUM(IF(ISNUMBER(SEARCH("-",TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",99)),(ROW(INDEX(XFD:XFD,1):INDEX(XFD:XFD,LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1))-1)*99+1,99)))),(2147 >= --LEFT(TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",99)),(ROW(INDEX(XFD:XFD,1):INDEX(XFD:XFD,LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1))-1)*99+1,99)),FIND("-",TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",99)),(ROW(INDEX(XFD:XFD,1):INDEX(XFD:XFD,LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1))-1)*99+1,99)))-1))*(2147<=--MID(TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",99)),(ROW(INDEX(XFD:XFD,1):INDEX(XFD:XFD,LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1))-1)*99+1,99)),FIND("-",TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",99)),(ROW(INDEX(XFD:XFD,1):INDEX(XFD:XFD,LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1))-1)*99+1,99)))+1,99)),--(2147 = --TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",99)),(ROW(INDEX(XFD:XFD,1):INDEX(XFD:XFD,LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1))-1)*99+1,99)))))>0

It is an array formula that needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

enter image description here

Upvotes: 2

Related Questions