# Thread: VBA Match Function (XP; SP3)

1. ## VBA Match Function (XP; SP3)

I am having an issue trying to replicate a two dimensional MATCH cell formula in VBA.

I have attached a sample file.

Thanks,
John

2. ## Re: VBA Match Function (XP; SP3)

Taking a union of ranges means that you create a range that consists of all cells that occur in at least one of the constituting ranges. This has nothing to do with concatenating values from two ranges.

Others will correct me if I'm wrong, but I don't think you can use the WorksheetFunction object here, you'll probably have to loop through the rows:

Sub TestTwoDimensional()
Dim lngRow As Long
For lngRow = 2 To 5
If Range("A" & lngRow) = "Two" And Range("B" & lngRow) = "Blue" Then
MsgBox "Found in row " & lngRow
Exit For
End If
Next lngRow
End Sub

3. ## Re: VBA Match Function (XP; SP3)

Not sure exactly what you are after. You can do it with a worksheet function (sorry, Hans), if you do as the array formula does and create the "intermediate" range first. It is not done with a UNION as you tried, it is done with a loop.

<pre>Sub TestTwoDimensional()
Dim oRngProd As Range
Dim oRngColor As Range
Dim oJoinedRng()
Dim oProd
Dim oColor
Dim lCount As Long
Dim af As WorksheetFunction
Dim x As Long
Dim oResult

Set oRngProd = Range("A2:A5")
Set oRngColor = Range("B2:B5")
oProd = "Two"
oColor = "Blue"

Set af = Application.WorksheetFunction
lCount = oRngProd.Count
ReDim oJoinedRng(1 To lCount)
For x = 1 To lCount
oJoinedRng(x) = oRngProd.Cells(x) _
& oRngColor(x)
Next
oResult = af.Match(oProd & oColor, oJoinedRng, 0)
End Sub</pre>

In practive I would have checking to ensure that the ranges were the same size. Also I think a function may be more appropriate, but it depends on what you are after...

For example with the function:
<pre>Function MultiVLookup(vValue, rng As Range)
Dim oJoinedRng()
Dim lCount As Long
Dim af As WorksheetFunction
Dim x As Long

Set af = Application.WorksheetFunction
lCount = rng.Rows.Count
ReDim oJoinedRng(1 To lCount)
For x = 1 To lCount
oJoinedRng(x) = rng.Cells(x, 1) _
& rng.Cells(x, 2)
Next

MultiVLookup = rng.Cells(af.Match(vValue, oJoinedRng, 0), 3)

End Function</pre>

You could use something like:
<pre>=multivlookup(E4&F4,A2:C5)</pre>

to yield "222-Blue" from your example. this is the same results you would get from the array formula (confirm with ctrl-shift-enter):

<pre>=INDEX(C2:C5,MATCH(E4&F4,A2:A5&B2:B5,0))</pre>

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
•