Condense formula

[McCainM]

I am looking for a way to be more efficient or condense the following formula. The goal is for it to search 2 consecutive cells to see if they contain the character in cell Settings!$A$5,R5. If yes, then it will return the value of another referenced cell. If not, it will look at the next two consecutive cells. It continues until it finds two consecutive cells that contains the character in cell Settings!$A$5,R5. The formula works, I just feel like there has to be a more efficient way to do it.

=IF(And((ISNUMBER(SEARCH(Settings!$A$5,Q5))),(ISNUMBER(SEARCH(Settings!$A$5,R5)))),Settings!$F$6,IF(And((ISNUMBER(SEARCH(Settings!$A$5,R5))),(ISNUMBER(SEARCH(Settings!$A$5,S5)))),Settings!$F$6,IF(And((ISNUMBER(SEARCH(Settings!$A$5,S5))),(ISNUMBER(SEARCH(Settings!$A$5,T5)))),Settings!$F$6,IF(And((ISNUMBER(SEARCH(Settings!$A$5,T5))),(ISNUMBER(SEARCH(Settings!$A$5,U5)))),Settings!$F$6,IF(And((ISNUMBER(SEARCH(Settings!$A$5,U5))),(ISNUMBER(SEARCH(Settings!$A$5,V5)))),Settings!$F$6,IF(And((ISNUMBER(SEARCH(Settings!$A$5,V5))),(ISNUMBER(SEARCH(Settings!$A$5,W5)))),Settings!$F$6,IF(And((ISNUMBER(SEARCH(Settings!$A$5,J2))),(ISNUMBER(SEARCH(Settings!$A$5,K2)))),Settings!$F$5,IF(And((ISNUMBER(SEARCH(Settings!$A$5,K2))),(ISNUMBER(SEARCH(Settings!$A$5,L2)))),Settings!$F$5,IF(And((ISNUMBER(SEARCH(Settings!$A$5,L2))),(ISNUMBER(SEARCH(Settings!$A$5,M2)))),Settings!$F$5,IF(And((ISNUMBER(SEARCH(Settings!$A$5,M2))),(ISNUMBER(SEARCH(Settings!$A$5,N2)))),Settings!$F$5,IF(And((ISNUMBER(SEARCH(Settings!$A$5,N2))),(ISNUMBER(SEARCH(Settings!$A$5,O2)))),Settings!$F$5,IF(And((ISNUMBER(SEARCH(Settings!$A$5,O2))),(ISNUMBER(SEARCH(Settings!$A$5,P2)))),Settings!$F$5,IF(And((ISNUMBER(SEARCH(Settings!$A$5,C2))),(ISNUMBER(SEARCH(Settings!$A$5,D2)))),Settings!$F$4,IF(And((ISNUMBER(SEARCH(Settings!$A$5,D2))),(ISNUMBER(SEARCH(Settings!$A$5,E2)))),Settings!$F$4,IF(And((ISNUMBER(SEARCH(Settings!$A$5,E2))),(ISNUMBER(SEARCH(Settings!$A$5,F2)))),Settings!$F$4,IF(And((ISNUMBER(SEARCH(Settings!$A$5,F2))),(ISNUMBER(SEARCH(Settings!$A$5,G2)))),Settings!$F$4,IF(And((ISNUMBER(SEARCH(Settings!$A$5,G2))),(ISNUMBER(SEARCH(Settings!$A$5,H2)))),Settings!$F$4,IF(And((ISNUMBER(SEARCH(Settings!$A$5,H2))),(ISNUMBER(SEARCH(Settings!$A$5,I2)))),Settings!$F$4,""))))))))))))))))))

 

[SanjayGMusafir]

Settings!$A$5

You are searching for a cell value throughout. Does it remain constant or it keep changing?

What exactly that cell value is that you search in cells - if you can give some suitable example to understand it better

 

[McCainM]

You are searching for a cell value throughout. Does it remain constant or it keep changing?

What exactly that cell value is that you search in cells - if you can give some suitable example to understand it better

It is currently a ✔ but is meant to be dynamic in that the user could change it. With that said, whether the value remains a ✔ or is changed by the user, the search for that character stays constant throughout the formula.

 

[SanjayGMusafir]

Try this and revert -

=IFS(
Countifs(Q5:R5,"*"&Settings!$A$5&"*"),Settings!$F$6,
Countifs(R5:S5,"*"&Settings!$A$5&"*"),Settings!$F$6,
Countifs(S5:T5,"*"&Settings!$A$5&"*"),Settings!$F$6,
Countifs(T5:U5,"*"&Settings!$A$5&"*"),Settings!$F$6,
Countifs(U5:V5,"*"&Settings!$A$5&"*"),Settings!$F$6,
Countifs(V5:W5,"*"&Settings!$A$5&"*"),Settings!$F$6,
Countifs(J2:K2,"*"&Settings!$A$5&"*"),Settings!$F$5,
Countifs(K2:L2,"*"&Settings!$A$5&"*"),Settings!$F$5,
Countifs(L2:M2,"*"&Settings!$A$5&"*"),Settings!$F$5,
Countifs(M2:N2,"*"&Settings!$A$5&"*"),Settings!$F$5,
Countifs(N2:O2,"*"&Settings!$A$5&"*"),Settings!$F$5,
Countifs(O2:P2,"*"&Settings!$A$5&"*"),Settings!$F$5,
Countifs(C2:D2,"*"&Settings!$A$5&"*"),Settings!$F$4,
Countifs(D2:E2,"*"&Settings!$A$5&"*"),Settings!$F$4,
Countifs(E2:F2,"*"&Settings!$A$5&"*"),Settings!$F$4,
Countifs(F2:G2,"*"&Settings!$A$5&"*"),Settings!$F$4,
Countifs(G2:H2,"*"&Settings!$A$5&"*"),Settings!$F$4,
Countifs(H2:I2,"*"&Settings!$A$5&"*"),Settings!$F$4,True,"")

 

[kevin9999]

If you were open to a VBA solution, then please try this on a copy of your workbook (put in a standard module). This code puts the answer into cell A1 - change that to suit your needs.

Option Explicit
Sub CheckConsec()
    Dim ws As Worksheet
    Set ws = Worksheets("Settings")       '<-- *** Check sheet name ***
    Dim a, b, c, i As Long, s As String
    a = ws.Range("Q5:W5"): a = Application.WorksheetFunction.Transpose(a)
    b = ws.Range("J2:P2"): b = Application.WorksheetFunction.Transpose(b)
    c = ws.Range("C2:I2"): c = Application.WorksheetFunction.Transpose(c)
    s = ws.Range("A5").Value
    
    For i = LBound(a) To UBound(a) - 1
        If a(i, 1) Like "*" & s & "*" And a(i + 1, 1) Like "*" & s & "*" Then
            ws.Range("A1") = ws.Range("F6")
            Exit Sub
        End If
    Next i
    
    For i = LBound(b) To UBound(b) - 1
        If b(i, 1) Like "*" & s & "*" And b(i + 1, 1) Like "*" & s & "*" Then
            ws.Range("A1") = ws.Range("F5")
            Exit Sub
        End If
    Next i
    
    For i = LBound(c) To UBound(c) - 1
        If c(i, 1) Like "*" & s & "*" And c(i + 1, 1) Like "*" & s & "*" Then
            ws.Range("A1") = ws.Range("F4")
            Exit Sub
        End If
    Next i
    ws.Range("A1") = "No consecutive matches found"
End Sub

 

[kvsrinivasamurthy]

Try

=IF(SUMPRODUCT(ISNUMBER(SEARCH(Settings!$A$5,Q5:V5))*ISNUMBER(SEARCH(Settings!$A$5,R5:W5)))>0,Settings!$F$6,IF(SUMPRODUCT(ISNUMBER(SEARCH(Settings!$A$5,J5:O5))*ISNUMBER(SEARCH(Settings!$A$5,K5:P5)))>0,Settings!$F$5,IF(SUMPRODUCT(ISNUMBER(SEARCH(Settings!$A$5,C5:H5))*ISNUMBER(SEARCH(Settings!$A$5,D5:I5)))>0,Settings!$F$4,"")))

 

[SanjayGMusafir]

Try this and revert -

=IFS(
Countifs(Q5:R5,"*"&Settings!$A$5&"*"),Settings!$F$6,
Countifs(R5:S5,"*"&Settings!$A$5&"*"),Settings!$F$6,
Countifs(S5:T5,"*"&Settings!$A$5&"*"),Settings!$F$6,
Countifs(T5:U5,"*"&Settings!$A$5&"*"),Settings!$F$6,
Countifs(U5:V5,"*"&Settings!$A$5&"*"),Settings!$F$6,
Countifs(V5:W5,"*"&Settings!$A$5&"*"),Settings!$F$6,
Countifs(J2:K2,"*"&Settings!$A$5&"*"),Settings!$F$5,
Countifs(K2:L2,"*"&Settings!$A$5&"*"),Settings!$F$5,
Countifs(L2:M2,"*"&Settings!$A$5&"*"),Settings!$F$5,
Countifs(M2:N2,"*"&Settings!$A$5&"*"),Settings!$F$5,
Countifs(N2:O2,"*"&Settings!$A$5&"*"),Settings!$F$5,
Countifs(O2:P2,"*"&Settings!$A$5&"*"),Settings!$F$5,
Countifs(C2:D2,"*"&Settings!$A$5&"*"),Settings!$F$4,
Countifs(D2:E2,"*"&Settings!$A$5&"*"),Settings!$F$4,
Countifs(E2:F2,"*"&Settings!$A$5&"*"),Settings!$F$4,
Countifs(F2:G2,"*"&Settings!$A$5&"*"),Settings!$F$4,
Countifs(G2:H2,"*"&Settings!$A$5&"*"),Settings!$F$4,
Countifs(H2:I2,"*"&Settings!$A$5&"*"),Settings!$F$4,True,"")

Just Realized we can further shorten it like below. I generally break a long formula in multiple lines to make it easy to read, understand or edit, if the need be.

Check and Revert -

=IFS(
OR(COUNTIFS(Q5:R5,"*"&Settings!$A$5&"*"),
   COUNTIFS(R5:S5,"*"&Settings!$A$5&"*"),
   COUNTIFS(S5:T5,"*"&Settings!$A$5&"*"),
   COUNTIFS(T5:U5,"*"&Settings!$A$5&"*"),
   COUNTIFS(U5:V5,"*"&Settings!$A$5&"*"),
   COUNTIFS(V5:W5,"*"&Settings!$A$5&"*")),Settings!$F$6,
OR(COUNTIFS(J2:K2,"*"&Settings!$A$5&"*"),
   COUNTIFS(K2:L2,"*"&Settings!$A$5&"*"),
   COUNTIFS(L2:M2,"*"&Settings!$A$5&"*"),
   COUNTIFS(M2:N2,"*"&Settings!$A$5&"*"),
   COUNTIFS(N2:O2,"*"&Settings!$A$5&"*"),
   COUNTIFS(O2:P2,"*"&Settings!$A$5&"*")),Settings!$F$5,
OR(COUNTIFS(C2:D2,"*"&Settings!$A$5&"*"),
   COUNTIFS(D2:E2,"*"&Settings!$A$5&"*"),
   COUNTIFS(E2:F2,"*"&Settings!$A$5&"*"),
   COUNTIFS(F2:G2,"*"&Settings!$A$5&"*"),
   COUNTIFS(G2:H2,"*"&Settings!$A$5&"*"),
   COUNTIFS(H2:I2,"*"&Settings!$A$5&"*")),Settings!$F$4,True,"")

 

[PdeB]

If you use Office 365 you could (H)stack the ranges and add a blank in between each of the ranges and (V)stack this range excluding the last and first value and use FIND (case sensitive, or SEARCH, case insensitive ) on that array and convert the array of booleans to numeric value, so it can be used in MMULT, which checks row-by-row in the stacked array if both values contain the search value. (If you don't want to search or find the value inside the cell's values, but you want it to equal, the formula could be simplified)

=LET(a,HSTACK(Q5:W5,"",J2:P2,"",C2:I2),OR(MMULT({1,1},N(ISNUMBER(FIND(A5,VSTACK(DROP(a,,-1),DROP(a,,1))))))=2))

 

[SanjayGMusafir]

There was a major error in my previous replies to your post.
COUNTIFS must be tested by a number to validate the result. Here is the corrected version. Try it and revert -

=IFS(
OR(COUNTIFS(Q5:R5,"*"&Settings!$A$5&"*")=2,
   COUNTIFS(R5:S5,"*"&Settings!$A$5&"*")=2,
   COUNTIFS(S5:T5,"*"&Settings!$A$5&"*")=2,
   COUNTIFS(T5:U5,"*"&Settings!$A$5&"*")=2,
   COUNTIFS(U5:V5,"*"&Settings!$A$5&"*")=2,
   COUNTIFS(V5:W5,"*"&Settings!$A$5&"*")=2),Settings!$F$6,
OR(COUNTIFS(J2:K2,"*"&Settings!$A$5&"*")=2,
   COUNTIFS(K2:L2,"*"&Settings!$A$5&"*")=2,
   COUNTIFS(L2:M2,"*"&Settings!$A$5&"*")=2,
   COUNTIFS(M2:N2,"*"&Settings!$A$5&"*")=2,
   COUNTIFS(N2:O2,"*"&Settings!$A$5&"*")=2,
   COUNTIFS(O2:P2,"*"&Settings!$A$5&"*")=2),Settings!$F$5,
OR(COUNTIFS(C2:D2,"*"&Settings!$A$5&"*")=2,
   COUNTIFS(D2:E2,"*"&Settings!$A$5&"*")=2,
   COUNTIFS(E2:F2,"*"&Settings!$A$5&"*")=2,
   COUNTIFS(F2:G2,"*"&Settings!$A$5&"*")=2,
   COUNTIFS(G2:H2,"*"&Settings!$A$5&"*")=2,
   COUNTIFS(H2:I2,"*"&Settings!$A$5&"*")=2),Settings!$F$4,True,"")