Some advanced Index Match (or something else?)

[Alhelor]

Hello folks,


please take a look at this production table. I need the formulas for A16 and D16:

Question: What is the earliest date at which a computer is consumed to create a car within the same project?


A Computer is consumed to produce a Car when they are both part of the same project.


What you can see in the table:


The earliest date at which a Computer is consumed is 01.01.1970. However, it’s project E which doesn’t produce Cars. Projects A and B are independent of each other. Projects C, D and F are the only ones where a Computer is consumed to produce a Car. In project F the relevant date cell is left blank and should be disregarded. In project D, the Computer is consumed earlier than in project C. Thus, D16 should return 18.11.1999 and A16 should return D.


Please help me with the formulas.


Thank you!

P.S.: The fact that in the dates some items are produced before their ingredients are consumed should be disregarded. The production date is not part of this task.

 

[Eric W]

Re: Some advanced Index Match (or something else?) - Please help!

I'm not sure I could reproduce your error without seeing the data either. I'll think about it, but see if you can create an example where it doesn't work and post it.

 

[inactiveuserps07]

Re: Some advanced Index Match (or something else?) - Please help!

@Alhenor, I don't know and have not checked into Eric W.'s suggested approach; but I can tell you that the 1.0.1900 is simply the number 0 rendered as a date. And that usually means that "FALSE" is turning up as a result somewhere in an IF() that doesn't have the value_if_false​ set. Hope that helps in your quest.

 

[Alhelor]

Re: Some advanced Index Match (or something else?) - Please help!

It seems to work. Can't tell exactly why it didn't. Thanks a lot for all your help!

 

[Alhelor]

Re: Some advanced Index Match (or something else?) - Please help!

Btw, I am still trying to understand the TRANSPOSE part. But do I understand it correctly that in Eric's formula IF(ISNUMBER(TRANSPOSE(Date)); is also obsolete if I'm disregarding the fact that something is produced before its ingredient is used (the underlined part in post #12).

 

[Eric W]

Re: Some advanced Index Match (or something else?) - Please help!

Yes, the IF(ISNUMBER(TRANSPOSE(date)) part checks to see if the consumed item has a valid date. If you don't care if it has a date, you can remove it. The ISNUMBER(date) part checks to see if the produced item has a valid date. You probably want to keep that, otherwise D12 will be an acceptable result.

Here's an alternate way to code it:

=IFERROR(SMALL(IF(($A$2:$A$13=TRANSPOSE($A$2:$A$13))*($B$2:$B$13=C18)*($C$2:$C$13="C")*(TRANSPOSE($B$2:$B$13)=B18)*(TRANSPOSE($C$2:$C$13)="P")*ISNUMBER($D$2:$D$13),$D$2:$D$13),1),"No match")

I'm not sure how easy it would be to explain how it works, since it involves matrix multiplication, which is a huge topic. But the first condition in red groups the lines for each project together (which creates a 2-D internal array), then the conditions without a TRANSPOSE work on the Produced conditions, and the conditions with the TRANSPOSE are for the Consumed conditions.