VBA Code for INDEX and MATCH – multiple criteria and multiple results

[zjghazal]

I have a table of multiple agents per country with their contact information I need to extract name and contact information of all available agents of the country name in F2 to G,H, and I columns from A,B,C, and D Table

My Macro is extracting the same Data.

My Macro code

Sub Get_Agent_Info()

Dim i As Integer
Dim lr As Integer
Dim namecol As Range
Dim Emailcol As Range
Dim Ctcol As Range
Dim cocol As Range
Dim Country As Range

lr = Cells(Rows.count, 2).End(xlUp).Row

Set cocol = Range("A2:A" & lr)
Set namecol = Range("B2:B" & lr)
Set Emailcol = Range("C2:C" & lr)
Set Ctcol = Range("D2:D" & lr)
Set Country = Range("$F$2")

For i = 2 To lr

On Error Resume Next

Cells(i, 7).Value = Application.WorksheetFunction.Index(namecol, Application.WorksheetFunction.match(Country, cocol, 0))
Cells(i, 8).Value = Application.WorksheetFunction.Index(Emailcol, Application.WorksheetFunction.match(Country, cocol, 0))
Cells(i, 9).Value = Application.WorksheetFunction.Index(Ctcol, Application.WorksheetFunction.match(Country, cocol, 0))

Next

End Sub



I hope this helps I attached a Screen shot too

Thank you
Zak

 

[Fluff]

Hi & welcome to MrExcel.
Why not just use a formula

=FILTER(B2:D100,A2:A100=F2)

 

[zjghazal]

Hi & welcome to MrExcel.
Why not just use a formula

=FILTER(B2:D100,A2:A100=F2)

I have other Macros running based on the results; where to plug above formula sorry I am a novice in VB

 

[Fluff]

You can out that in G2 after clearing all the cells in columns G, H & I from row 2 down.