Vlookup on Dynamic Range

[hamidxa]

I'm trying to find a way to perform Vlookups against a dynamic range of data, where the number of rows/values in Col B is always subject to change.

The way that the workbook is structured is as follows:
Sheet1: Except for Cols A & B, this is a blank sheet. This is the sheet in which the vlookup values will need to fall into based on the number found in its' Column B
Sheet2: The sheet that contains the data that will be passed into Sheet1 via vlookup, and all of its Columns contain data.

So, basically, Sheet1 is a shell that needs to be filled with data copied over from Sheet2 based on vlookups against Col B.

The vlookups will need to be shifted back 1 column.
And examples of how the Vlookups need to work is:

• The value in Sheet2 [Col E], needs to get passed into Sheet1 [Col D]

• Along those lines:
  The value in Sheet2 [Col F], needs to get passed into Sheet1 [Col E]
  The value in Sheet2 [Col G], needs to get passed into Sheet1 [Col F]

• so on and so forth
  

• So basically, all of the values passed into Sheet1 from Sheet2 need to be shifted back by 1 column until we reach the last Col (Col M in this file).
  Where
  The value in Sheet2 [Col M], needs to get passed into Sheet1 [Col L]

The number of columns will remain fixed, but again the number rows will vary week to week in both of the two sheets, so I would need to have a way of creating a Vlookup through VBA that accounts for the dynamic range in Col B.

Attached is a sample file that will hopefully make this a bit more clear.

I would greatly appreciate any help on this!

[Norie]

Perhaps.

Please Login or Register  to view this content.

[ursanil]

Please check the attached I guess it will solve the problem

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[hamidxa]

Thank you all for providing your solutions. These are all great!
I landed on the solution that Norie provided, but it's helpful to see all of the different approaches as well.

Thanks again all!

Problem solved.