Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    225
    Thanks
    30
    Thanked 4 Times in 4 Posts

    Is there a version of "MATCH" that works with multiple columns?

    Hello experts,

    I have a 2 dimensional array of bits of text. I want to find the row and column of one of those bits of text.

    I thought MATCH would do it - but that only works if you have a single row or column to search on.

    So, is there something that will work for multiple rows and column?

    For example: My data sits in cols B though to L and rows 3 though to 94.

    This gives me N/A: "=MATCH("HPC0117XP",B3:L94,0)"

    This works (assuming my data is in row G): =MATCH("HPC0117XP",G3:G94,0)

    I want something that will make the first example work that I can use as a function

    Thanks for any ideas

    Alan

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,414
    Thanks
    208
    Thanked 835 Times in 768 Posts
    Alan,

    Here's a User Defined Function (UDF) that will do what I think you are asking.
    Code:
    Option Explicit
    
    Function MyFind(rngSearch As Range, zFind As String) As String
    
        Dim rngCell As Range
        
        For Each rngCell In rngSearch.Cells
        
           If rngCell.Value = zFind Then
             MyFind = rngCell.Address(, , xlA1)
             Exit Function
           End If
        Next rngCell
        
        MyFind = "#NF"
    
    End Function
    MyFind.JPG
    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. #3
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,294
    Thanks
    47
    Thanked 257 Times in 237 Posts
    Alan,

    You could also paste the following formula where you want the row:column listed. Here, the formula is placed in B2 and matches the value in B1.
    Cell B2 =CONCATENATE(SUMPRODUCT((B3:L94=$B$1)*ROW(B3:L94)) ,":",SUMPRODUCT((B3:L94=$B$1)*COLUMN(B3:L94)))

    match2.png

    HTH,
    Maud
    Last edited by Maudibe; 2014-07-21 at 07:06. Reason: updated formula base on astute obsrervation by Alan

  4. #4
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    225
    Thanks
    30
    Thanked 4 Times in 4 Posts
    Thanks both - I like both answers but I have no idea why SUMPRODUCT works. Also, Maud, your text doesn't quite match the formula - the value is in B1, but your formula has $A$1 in it.

    Alan
    Last edited by alan sh; 2014-07-21 at 03:46.

  5. #5
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,294
    Thanks
    47
    Thanked 257 Times in 237 Posts
    Alan,

    I changed the criteria in cell a1 to B1 but didn't change it in the formula that I listed. Please note the formula in the formula bar in the picture

    I made the update in my original post.
    Thx
    Last edited by Maudibe; 2014-07-21 at 07:05.

  6. #6
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    225
    Thanks
    30
    Thanked 4 Times in 4 Posts
    Yes, I gathered that (later on).

    Cheers

    Alan

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,414
    Thanks
    208
    Thanked 835 Times in 768 Posts
    Maud & Alan,

    Just a note since I'm not familiar with Alan's data set but if there are repeated values in the range the SumProduct solution (which is very COOL Maud!) will return a reference to a cell that does not contain the searched for value. The UDF solution on the other hand will ONLY return the first occurrence of the searched for value! So neither solution is perfect if there are duplicates in the search range. HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  8. #8
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,294
    Thanks
    47
    Thanked 257 Times in 237 Posts
    Then is there a middle ground that will list them all?

    Match3.png

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Dim rngCell As Range
        Dim RngSearch As Range
        Dim result As String
        Set RngSearch = Range("B3:L94")
        result = ""
        For Each rngCell In RngSearch.Cells
            If rngCell.Value = Range("B1") And result <> "" Then
                result = result & ", " & rngCell.Address(, , xlA1)
            ElseIf rngCell.Value = Range("B1") Then
                result = rngCell.Address(, , xlA1)
            End If
        Next rngCell
        Range("B2") = result
    End Sub

  9. The Following User Says Thank You to Maudibe For This Useful Post:

    RetiredGeek (2014-07-21)

  10. #9
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    225
    Thanks
    30
    Thanked 4 Times in 4 Posts
    Thanks all. Very interesting stuff.

    I modified RG's original to give me back the row and column number and it works great.

    However, don't pass a range that is all the columns or it will loop forever if it can't find a match (guess how I knew that).

    Cheers

    Alan

  11. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    If you are looking for function you can use this UDF:
    Code:
    Option Explicit
    Function MatchAll(vValue, rLookup As Range)
      Dim rCell As Range
      Dim sAddr As String
      sAddr = ""
      For Each rCell In rLookup
        If rCell = vValue Then
          sAddr = sAddr & ", " & rCell.Address(False, False)
        End If
      Next
      If sAddr = "" Then
        MatchAll = CVErr(xlErrNA)
      Else
        MatchAll = Mid(sAddr, 3)
      End If
    End Function
    Call it in a cell like:
    =MatchAll("HPC0117XP",B3:L94)

    It will work with 1 match or many to get a list of the cell addresses of each that exactly match. If no match is found it will give a #N/A error.

    Steve

  12. #11
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Elegant bit of code Steve.
    Regards
    Don

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •