Need a formula to copy value x from a cell in another sheet in row 1 and 2 if the value in another cell equates to certain value

[reemajhs]

I am trying to write a formula with below conditions:

Data in Sheet 1
Vehicle Street
1 Street1
2 Street2
3 Street3
4 Street1
5 Street1

If vehicle1=Street1 copy value in cell Sheet1.A2 in Sheet2.A2
If vehicle1=Street1 copy value in cell Sheet1.A2 in Sheet2.A3
If vehicle2=Street2 copy value in cell Sheet1.A2 in Sheet2.A4
If vehicle3=Street3 copy value in cell Sheet1.A2 in Sheet2.A5
If vehicle4=Street1 copy value in cell Sheet1.A2 in Sheet2.A6
If vehicle4=Street1 copy value in cell Sheet1.A2 in Sheet2.A7

So whereever there is a reference to Street1, I need to copy the text against street1 in two rows and then move on to check if there is no reference to Street1, then copy the cell reference in column A only once.

I tried using CELL, VLOOKUP, IF, INDIRECT but none works consistently.

I tried using a PrevSheet macro but I dont know how to read macro so couldnt modify it as per my requirement.

Any help will be much appreciated