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

1. ## 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. 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
Exit Function
End If
Next rngCell

MyFind = "#NF"

End Function```
MyFind.JPG
HTH

3. 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

4. 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

5. 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

6. Yes, I gathered that (later on).

Cheers

Alan

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

8. 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
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. 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. If you are looking for function you can use this UDF:
Code:
```Option Explicit
Function MatchAll(vValue, rLookup As Range)
Dim rCell As Range
For Each rCell In rLookup
If rCell = vValue Then
End If
Next
MatchAll = CVErr(xlErrNA)
Else
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. Elegant bit of code Steve.

#### Posting Permissions

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