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

Topic

New Reply

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

Reply | Quote

Replies

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

37446-MyFind
HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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)))

37450-match2

HTH,
Maud

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Yes, I gathered that (later on).

Cheers

Alan

Reply | Quote

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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Then is there a middle ground that will list them all?

37451-Match3

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Elegant bit of code Steve.

Reply | Quote