Macro to copy data from master sheet if match is found

Hi..
My problem is as below..
Match the values in Column B of worksheet 2 with column B of worksheet 1. If match is found, select the matched cell of worksheet 1, copy the value from column W from corresponding row and paste it in Y column of worksheet 2. Below is format of data:

Worksheet 1:
Column B Column W
50 Bulb
51 Lamp
52 Tubelight
53 Bulb
54 Lamp

Worksheet 2:
Column B Column Y
2
4
50 Bulb
60
70

I want to use index and match function. But I am not able to write formula for that.
Kindly help me in this regard.
Thanks for time spent in going through this post and for replying..

or try this :

Please Login or Register  to view this content.

Thanks for your reply..but I want to write this in macro..I am new to VBA..
I tried this

Sht1Rng = Sheets(1).Range("B65536").End(xlUp).Row
Sht2Rng = Sheets(2).Range("B65536").End(xlUp).Row
For i = Sht2Rng To 1 Step -1
Sheets(2).Cells(i, 25).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(INDEX(Sheet1!$W:$W,MATCH(Sheet2!B2,Sheet1!$B:$B,0)),"")"
Next i

but the above code is not working..what modifications I have to do?

Thanks for your reply..but I want to write this in macro..I am new to VBA..
I tried this

Sht1Rng = Sheets(1).Range("B65536").End(xlUp).Row
Sht2Rng = Sheets(2).Range("B65536").End(xlUp).Row
For i = Sht2Rng To 1 Step -1
Sheets(2).Cells(i, 25).Select
ActiveCell.FormulaR1C1 = _
"=INDIRECT("Sheet1!R2C23" & MATCH(R2C2,Sheet1!R2C2:R50000C2,0))"
Next i

but the above code is not working..what modifications I have to do?

This code works as long as you do not mind with the speed if you have large data

Please Login or Register  to view this content.

Thanks for your reply, AB33..
My data is very large hence I need a macro which has less execution time..
I have tested below code..its working but the issue is of speed..due to 2 for loops its taking time to execute. I want to eliminate 1 for loop. Any help in this regard will be much appreciated.

Sht1Rng = Sheets(1).Range("B65536").End(xlUp).Row
Sht2Rng = Sheets(2).Range("B65536").End(xlUp).Row

For i = Sht2Rng To 1 Step -1
For J = Sht1Rng To 1 Step -1
If Sheets(1).Cells(J, 2) = Sheets(2).Cells(i, 2) Then
Sheets(2).Cells(i, 25) = Sheets(1).Cells(J, 23)
End If
Next J
Next i

I know my code is slow, but your code is much slower than mine

If you do not have duplicates, this code is much faster than range looping

Please Login or Register  to view this content.

Thanks again AB33..
I am new to vba..just want to know is it possible to use inbuilt functions such as index and match to above code? If yes, how should I modify the macro?

index and match,no as far as I know. Dictionary script can deal with unique values ONLY. With Index and Match there are ways of returning duplicate values. Why do you need to use a code? I and M can simpley do your task.

Oli,
The OP is looking for ideal solution! IMO, it does not exist. The Find function can cope with duplicates, but very slow with large data and dictionary can not cope with duplicates. I would prefer to use a simple Excel I and M function, with out the complication of codes.

Hi..sorry for confusion..but I want to use index and match functions in vba code.

From your posting on #4, i think this what you are looking for :

Please Login or Register  to view this content.

From your posting on #4, i think this what you are looking for :

Please Login or Register  to view this content.

Thank you all for your timely help..
Below code with some modifications is working fine but somehow this code is also considering headers of the columns and matching the values. I want to start from row 2. I tried but could not modify. Can you please help me in this regard?

Originally Posted by AB33

This code works as long as you do not mind with the speed if you have large data

Please Login or Register  to view this content.

Below code is not extracting values from Column W of mastersheet..

Originally Posted by SDCh

From your posting on #4, i think this what you are looking for :

Please Login or Register  to view this content.

Thanks a lot..made some changes..now its working like a charm..

Originally Posted by SDCh

From your posting on #4, i think this what you are looking for :

Please Login or Register  to view this content.

Glad to know your problem has been solved