Compare value if matched copy another cell

[IFeelYourPain]

I have a set up in two different worksheets.

[WS1]
7458 Completed
5487 Not Completed
5687 Completed
1258 Completed


[WS2]
7458
7777
8987
1452
5487
8957


If WS2 has a value in A column that matches the A column of WS1, then I want it to copy the value of Column B in WS1. This needs to be done for multiple instances and pasted into a new worksheet. How can I go about doing this?

 

[onlyadrafter]

Hello,

can be achieved by formula:

In WS2 (assumed 7458 is in cell A1), cell B1, enter this formula =IF(ISERROR(VLOOKUP(A1,Sheet1!A:B,2,0)),"",VLOOKUP(A1,Sheet1!A:B,2,0)) and copy down as far as required.

Is this acceptable?

 

[IFeelYourPain]

The thing is I don't know how far down it will need to go?

 

[onlyadrafter]

Hello,

VB solution

Sub CHECK_VALUES()
    With Sheets("Sheet2")
        .Range("B1").Formula = "=IF(ISERROR(VLOOKUP(A1,Sheet1!A:B,2,0))," & """""" & ",VLOOKUP(A1,Sheet1!A:B,2,0))"
        .Range("B1").Copy Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    End With
End Sub

 

[IFeelYourPain]

It created a new column and it says status for the title, but the cells below it are blank.

 

[IFeelYourPain]

Actually I got it to work, but is there anyway instead of copying the content, it would instead put y or no if completed or not completed? Sometimes there is also information after completed, so it can't be an exact match. Not sure how to do that?

 

[onlyadrafter]

Hello,

Amending the code previously supplied:

Sub CHECK_VALUES_1()
    With Sheets("Sheet2")
        .Range("B1").Formula = "=IF(ISERROR(VLOOKUP(A1,Sheet1!A:B,2,0))," & """""" & _
            ",IF(LEFT(VLOOKUP(A1,Sheet1!A:B,2,0),9)=" & """" & "Completed" & """" & "," & """" & "y" & """" & _
       ",IF(LEFT(VLOOKUP(A1,Sheet1!A:B,2,0),13)=" & """" & "Not Completed" & """" & "," & """" & "no" & """" & ",)))"
        .Range("B1").Copy Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    End With
End Sub

Does it work as expected?