VLOOKUP doubt

I have two sheets Sheet1 and Sheet2.

In sheet Sheet1, column A is a concatenation of columns B and C.
In sheet Sheet2, again, column A is a concatenation of columns B and C.

For each cell in Sheet2!ColumnA, I want to check if the value exists in Sheet1!ColumnA, and if there is a match I want to retrieve the value from the Column D of the same row from Sheet1.

I tried doing this with the following code:

Please Login or Register  to view this content.

But since this code retrieves the value from the column A of both the sheets as a formula =A2&B2, I am getting the return value as 0.

I tried RC[-3].Value, but got some error.

I am new to excel macro. Can somebody help me fixing this issue. Thanks in advance.

Much better to use the .Find & .Findnext methods in VBA, look at the Help example in the VBA help files.