Help - Lookup, Match, Return Specific Value

[ravin82]

Hello,
I'm novice in excel formulae. Looking for a solution for the below problem
There are set of tasks for each product launch with due dates. For reporting purposes, I need some formula for column "Product Launch" where excel can
1) Lookup due date of "product launch" task (6th task of every product)
2) Map the due date to specific product (Ex: product X here)
3) Apply that specific product launch due date to ALL TASKS related to product X
Let's say, I'll have running excel with 100 products each having 6 tasks & different launch dates, that's total 600 repetitive tasks in single sheet. For segregating products & related tasks by 'Launch Date' I need this additional column with formula

Product Name	Task	Due Date	Product Launch
Product X	Product requirements gathering	2/1/2020​	5/15/2020​
Product X	Product design	3/15/2020​	5/15/2020​
Product X	Product prototype ready	3/30/2020​	5/15/2020​
Product X	Product testing	4/15/2020​	5/15/2020​
Product X	Product mass production	4/30/2020​	5/15/2020​
Product X	Product launch	5/15/2020​	5/15/2020​
Prod Y	Product requirements gathering	3/30/2020​	6/15/2020​
Prod Y	Product design	4/15/2020​	6/15/2020​
Prod Y	Product prototype ready	4/30/2020​	6/15/2020​
Prod Y	Product testing	5/15/2020​	6/15/2020​
Prod Y	Product mass production	5/30/2020​	6/15/2020​
Prod Y	Product launch	6/15/2020​	6/15/2020​

Thanks,
Ravi N

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff New.xlsm
	A	B	C	D
1	Product Name	Task	Due Date	Product Launch
2	Product X	Product requirements gathering	01/02/2020	15/05/2020
3	Product X	Product design	15/03/2020	15/05/2020
4	Product X	Product prototype ready	30/03/2020	15/05/2020
5	Product X	Product testing	15/04/2020	15/05/2020
6	Product X	Product mass production	30/04/2020	15/05/2020
7	Product X	Product launch	15/05/2020	15/05/2020
8	Prod Y	Product requirements gathering	30/03/2020	15/06/2020
9	Prod Y	Product design	15/04/2020	15/06/2020
10	Prod Y	Product prototype ready	30/04/2020	15/06/2020
11	Prod Y	Product testing	15/05/2020	15/06/2020
12	Prod Y	Product mass production	30/05/2020	15/06/2020
13	Prod Y	Product launch	15/06/2020	15/06/2020
14	Prod Z	Product requirements gathering	01/01/2020	01/06/2020
15	Prod Z	Product design	01/02/2020	01/06/2020
16	Prod Z	Product prototype ready	01/03/2020	01/06/2020
17	Prod Z	Product testing	01/04/2020	01/06/2020
18	Prod Z	Product mass production	01/05/2020	01/06/2020
19	Prod Z	Product launch	01/06/2020	01/06/2020
Sheet2

Cell Formulas
Range	Formula
D2:D19	D2	=SUMIFS(C:C,A:A,A2,B:B,"Product launch")

 

[Fluff]

Cross posted Help - Lookup, Match, Return Specific Value

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[ravin82]

Hello, the formula you provided actually works. Just to add one more related query, what if the text "Product Launch" is just a part of the whole text and I need to find & match this text in that entire cell value? condition like "if you find this text, map the due date to specific product & return due date for all the tasks for that product"

And, about cross-posting, I'm pretty new here. Will take care of it next time

Thanks,
Ravi N

 

[Fluff]

Just use
=SUMIFS(C:C,A:A,A2,B:B,"*Product launch*")

 

[ravin82]

Thank you very much. It works

 

[Fluff]

You're welcome & thanks for the feedback.