• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

use lookup function or macro

M. Frank

New Member
Hello,
I need help for this Excel question, since I have tried many things and formulas, but I could not solve the problem.

Can you help me to composed the necessary correct function?
I send you here the workbook excel file where I have explained step by step the functionality.

I hope that you can resend me a appropriate solution.
Many thanks.

Frank
 

Attachments

bosco_yip

Excel Ninja
Maybe.............

1] In D6, copied down to D8 :

=INDEX($K$6:$M$11,MATCH(C6,$H$6:$H$11,0)+1,MATCH(LOOKUP(9^9,$D$1:$D5),INDEX($K$6:$M$11,MATCH(C6,$H$6:$H$11,0),0),1))

Then,

2] Select D6:D8 >> copy/paste to D15:D17 and D24:D26

Regards
Bosco
 

Attachments

Last edited:

M. Frank

New Member
Msr. Bosco


Many, many, many thanks by me!!!!!

The complex function operates very, very well !

I have only modified the function with the conditional method below, this to prevent #N/A or erroneous data when the cell “ex. D4” is empty:


=IF(D$4<>"";INDEX($K$6:$M$11;MATCH(C6;$H$6:$H$11;0)+1;MATCH(LOOKUP(9^9;$D$1:$D5);INDEX($K$6:$M$11;MATCH(C6;$H$6:$H$11;0);0);1));"")

Now I will ask you just last question, if it’s possible.

I want to copy an empty section/array “ex. C3:D8” and then past it more time progressively in column of sheet-1.

How to modify the function in order that it work accurately with own new cell “Product production date” in automatic?

I send you my admiration for your important mission, your very high professionality, and to this your courtesy.


I await your message.


Frank
 

bosco_yip

Excel Ninja
Msr. Bosco


Many, many, many thanks by me!!!!!

The complex function operates very, very well !

I have only modified the function with the conditional method below, this to prevent #N/A or erroneous data when the cell “ex. D4” is empty:


=IF(D$4<>"";INDEX($K$6:$M$11;MATCH(C6;$H$6:$H$11;0)+1;MATCH(LOOKUP(9^9;$D$1:$D5);INDEX($K$6:$M$11;MATCH(C6;$H$6:$H$11;0);0);1));"")

Now I will ask you just last question, if it’s possible.

I want to copy an empty section/array “ex. C3:D8” and then past it more time progressively in column of sheet-1.

How to modify the function in order that it work accurately with own new cell “Product production date” in automatic?

I send you my admiration for your important mission, your very high professionality, and to this your courtesy.


I await your message.


Frank
To control the "Product production date" when in empty,

try this revised formula in D6 and copied down to D8 :

=IF(LOOKUP(2,1/($D$2:$D5<>""),$D$1:$D4)="","",INDEX($K$6:$M$11,MATCH($C6,$H$6:$H$11,0)+1,MATCH(LOOKUP(9^9,$D$1:$D5),INDEX($K$6:$M$11,MATCH($C6,$H$6:$H$11,0),0),1)))

Regards
Bosco
 

M. Frank

New Member
Goodmorning Mrs. Bosco.

I don't why.
To simulate an example: if I change the text in merged cell "H10:H11" of Sheet-2 (ex. from "C" to "Cc") the cells (ex. D7 and D8) of Sheet-1 return #N/A.

Can you explain me?

Thank you.

Frank
 
Top