VBA vlookup based on criteria

[ahmed.sange]

Hi ,

I'm looking for some assistance in developing a macro for the below:

1. I have two workbooks (book1 & book2) with a primary key (i.e. a common column of data - i.e. item code). Both the workbooks are dynamic (i.e. there may be new columns and rows added onto both the workbooks). Book 1 contains the data/table from where I want to vlookup data onto book 2 based on conditions. Please view the attached 2 workbooks.
   
2. I want the macro to find the 'month' in book1 and then vlookup that data onto the same month column of book2 based on the item code. (e.g. To vlookup august data on book2, I want the macro to find august on book1 and then vlookup data based on the item code)
3. This is because there will be new columns (i.e. new month columns) added to book1 on a monthly basis.

Please help as all my attempts have failed.

 

[prasaddn]

Hi,

If you can arrange the headings to be consistent in both the books, then you can acheive this without any macros.

for example, in book 1, i changed the "August " to "August", and in book2 i changed "August (2014)" to "August". Similarly for other months headings as well.

And, then using the below formula you get the desired results:

=SUMPRODUCT(('[Book2_-_Sample(1).xlsx]Sheet1'!$B$3:$B$19=$B5)*('[Book2_-_Sample(1).xlsx]Sheet1'!$I$1:$AB$1=G$2)*('[Book2_-_Sample(1).xlsx]Sheet1'!$I$3:$AB$19))

Regards,
Prasad DN.

 

[ahmed.sange]

Hi Prasad,

Thanks for the input.
However, the headers or columns cannot be consistent. Because, Book1 would have new columns added every month.
Thus, I need to develop a macro that identifies the month on Book1 and accordingly updates information on Book2 where the lookup value would be the item code.