CODEWITHSUNDEEP
excelsearch
user1995
user1995

Reputation: 538

Match in excel with multiple values in query cell

I have a column that has a list of genomes which are special. There is another column of genomes in which some cells have multiple entries, comma-separated. What I want is to check if for each cell of the second column, is any of its entries in the first column. If the cells in second column only had one element, I could do it with =NOT(ISERROR(MATCH(B2,$A$2:$A$120,0))) but since some query cells have muliple elements, this will not work for those. Any suggestions?

enter image description here

Upvotes: 0

Views: 491

Answers (2)

basic
basic

Reputation: 11968

Try SUMPRODUCT and SEARCH:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(","&$A$2:$A$5&",",","&B2&","))),"Y","N")

enter image description here

Explanation:

I will explain logic for cell C2 in my example.

  1. In first step formula got values from ranges and results into:

    =IF(SUMPRODUCT(--ISNUMBER(SEARCH({",AA,",",CC,",",EE,",",Gg,"},",AA,BB,"))),"Y","N")

  2. SEARCH function returns array of positions of found strings or error if not found:

    =IF(SUMPRODUCT(--ISNUMBER({1,#VALUE,#VALUE,#VALUE})),"Y","N")

  3. ISNUMBER returns array of booleans:

    =IF(SUMPRODUCT(--{TRUE,FALSE,FALSE,FALSE}),"Y","N")

  4. -- converts booleans to integers:

    =IF(SUMPRODUCT({1,0,0,0}),"Y","N")

  5. SUMPRODUCT sums it up:

    =IF(1,"Y","N")

  6. IF returns TRUE part if first argument is nonzero value.

Upvotes: 1

Luis Castro
Luis Castro

Reputation: 11

If you need to keep the rows in the genomes column, then Separate genomes column in 3 columns and do the MATCH in 3 new columns B,C & D.

=NOT(ISERROR(MATCH(A2,$B$2:$D$120,0)))

Edit: or you can try to use a VB macro and a regular expression:

Public Function RegexExecute(str As String, reg As String, _
                             Optional matchIndex As Long, _
                             Optional subMatchIndex As Long) As String
    On Error GoTo ErrHandl
    Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = reg
    regex.Global = Not (matchIndex = 0 And subMatchIndex = 0) 'For efficiency
    If regex.test(str) Then
        Set matches = regex.Execute(str)
        RegexExecute = matches(matchIndex).SubMatches(subMatchIndex)
        Exit Function
    End If
ErrHandl:
    RegexExecute = CVErr(xlErrValue)
End Function

reference

Upvotes: 1

Related Questions