Index match VBA

[anand3dinesh]

Hi i'm a beginner can you please help me to wright VBA script for "Index match" that returns value from other worksheet?
please refer images i have 2 worksheets a and B in sheet b i need code that fills cells according to the column heading

 

[Fluff]

You're welcome & thanks for the feedback

 

[anand3dinesh]

You're welcome & thanks for the feedback

this code works only one column i need to fill all the cells as per columns

 

[Fluff]

Oops, it should be

Sub anand3dinesh()
    With Sheets("Sheet2")
        With .Range("B2:D" & .Range("A" & Rows.Count).End(xlUp).Row)
            .Formula = "=INDEX(Sheet1!$B$2:$D$9,MATCH($A2,Sheet1!$A$2:$A$9,0),MATCH(B$1,Sheet1!$B$1:$D$1,0))"
            .Value = .Value
        End With
    End With
End Sub

 

[anand3dinesh]

Oops, it should be

Sub anand3dinesh()
    With Sheets("Sheet2")
        With .Range("B2:D" & .Range("A" & Rows.Count).End(xlUp).Row)
            .Formula = "=INDEX(Sheet1!$B$2:$D$9,MATCH($A2,Sheet1!$A$2:$A$9,0),MATCH(B$1,Sheet1!$B$1:$D$1,0))"
            .Value = .Value
        End With
    End With
End Sub

Perfect thanks a lot

 

[Fluff]

My pleasure & thanks for the feedback

 

[anand3dinesh]

one more doubt i need to return data with the help of other columns, suppose i create a unique identification number in middle of the table referring that column it should fill right and left columns

 

[anand3dinesh]

My pleasure & thanks for the feedback

one more doubt i need to return data with the help of other columns, suppose i create a unique identification number in middle of the table referring that column it should fill right and left columns

 

[Fluff]

I'm afraid I haven't got a clue what you're talking about.
I suggest you download & install the XL2BB add-in & then use that to post some sample data in your reply with an explanation.

Also please do not duplicate your posts.

 

[anand3dinesh]

I'm afraid I haven't got a clue what you're talking about.
I suggest you download & install the XL2BB add-in & then use that to post some sample data in your reply with an explanation.

Also please do not duplicate your posts.

please refer image i have created new column called "Code" it is in between, referring that column other column should fill

 

[Fluff]

You need to change the references from D to your last column, both in the Range & in the formula