Vlookup of merged cells & getting all adjacent Row values of a column

[Sandip Roy]

Dear friends,

Kindly go through the attached workbook

In FEB 17 sheet A column is merged differently & is carrying Item names. Suppose For Product UD10 it is merged from B7 to B14 &
for UD20 it is merged from B15 to B22. And it is continued in this way with lots of product names

C column is carrying names of Raw Materials of those products.

In A1 of sheet1 I want to mention either UD10 or UD20 or whatever required.

I need that when I mention that product name in A1 in Sheet2, from B2 the names of Raw materials would come automatically (ONLY THOSE NAMES WHICH ARE MENTIONED IN FEB17 SHEET AGAINST THAT PRODUCT ONLY, not more than that)[ in this case formula will be in B2] & from C1 its MTD figures will be shown.

And in D1 if I mention a date figures will be shown for that particular date of that particular item. I have highlighted yellow my desired result.

Please help
Regards
Sandip

 

[Sandip Roy]

Please note that number of merged cells will be variable, not constant.

 

[bosco_yip]

Hi,

1] Select cell A1 dropdown list

2] Check output results as per attached file

Regards
Bosco

 

[Sandip Roy]

Hi Bosco,
Sorry Friend, I was little sick, couldn't able to visit. However I had seen your workbook but unfortunately not working properly once am changing from drop down list. All the results are showing N/A.
Even when returning back to earlier product, it is also showing the same.

Regards
Sandip

 

[bosco_yip]

Hi Bosco,
Sorry Friend, I was little sick, couldn't able to visit. However I had seen your workbook but unfortunately not working properly once am changing from drop down list. All the results are showing N/A.
Even when returning back to earlier product, it is also showing the same.

Regards
Sandip

Hi Sandip,

My Post #3 file used a AGGREGATE function of which introduced since EXCEL 2010, that may caused your old version Excel file showing N/A.

I herein attached a revised file of using SMALL function instead which will suit with the old version Excel.

Regards
Bosco

 

[Sandip Roy]

Hi Bosco,

Wooo !!! Now it's ok, gr8 friend, thanks.
Regards
Sandip