Getting values off another sheet by references!?

Hello,

please have a quick look at my attachment: I need to fill the columns B-D of the sheet "starting data" with the values matching
the IDs of the sheet "reference". The result should look like shown in sheet "result after code".

You can also find my somewhat working solution inside (press the arrow), but its performance is too bad, it takes several minutes
to run on the actual lists wich contain 20,000 - 30,000 rows each, wich is not acceptable.

Can you guys help me out with a better working solution to my problem?

Hello Dibbins thank you for your answer and sorry for the late reply,

The problem is, I cannot add formulas or anything else manually after starting the code.
I tryed implementing the vlookup using VBA but it won't let my no matter how hard I try:

Please Login or Register  to view this content.

What am I missing??

Hi bydo,
try this

Please Login or Register  to view this content.

FWIW:

Please Login or Register  to view this content.

Hello nilem, hello john,
thank you for your answers, both codes work perfectly, I prefer john's code because it runs a lot faster!

If anybody else is using this in the future be aware that john forgot to merge the city and street in the last column,
a minor error and very easy to fix, you figure.

I have one last quesion: what exactly is the purpose of the digits in these statements?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

You're welcome. Glad to help out and thanks for the feedback.

.End(3)(1).Row and .End(3).Row - actually they both mean the same thing. .End(3) you'll more often see as .End(xlUp). The (1) is not necessary I was just in a hurry writing the code. They chose the last row with data in a column.

Thanks again, you helped me a lot!