Hi
I have the following data. I wants to find the maximum data from End Date column based on matching multiple and randon values in predecessor column. Values in predecessor columns are based on task number in column F.
I tried several formulas but no Joy. Any Help from experts please.
I have the following data. I wants to find the maximum data from End Date column based on matching multiple and randon values in predecessor column. Values in predecessor columns are based on task number in column F.
I tried several formulas but no Joy. Any Help from experts please.
| Book1.xlsx | |||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | |||
| 2 | |||||||||||||||||||||||||||||
| 3 | =INDEX($H$6:$H$21, MATCH(1, ($F$6:$F$21 = I6) * (ROW($F$6:$F$21) >= ROW(I6)), 0)) requires cnt+shift+entre | ||||||||||||||||||||||||||||
| 4 | =IFERROR(AGGREGATE(14, 6, INDEX($H$6:$H$21, MATCH(I6, $F$6:$F$21, 0)) / (($I$6:$I$21 = I6) * (INDEX($H$6:$H$21, MATCH(I6, $F$6:$F$21, 0)) <> "")), 1), "No match found") works for single value in column I, ct+shi+entre | ||||||||||||||||||||||||||||
| 5 | Task | Start Date | End Dtae | Predecessors | Result | ||||||||||||||||||||||||
| 6 | 1 | 9/2/23 | 16/2/23 | 2,3 | No match found | ||||||||||||||||||||||||
| 7 | 2 | 9/2/23 | 16/2/23 | 6 | No match found | ||||||||||||||||||||||||
| 8 | 3 | 20/2/23 | 27/2/23 | 7 | No match found | ||||||||||||||||||||||||
| 9 | 4 | 23/1/23 | 30/1/23 | 7 | No match found | ||||||||||||||||||||||||
| 10 | 5 | 31/1/23 | 7/2/23 | 8 | No match found | ||||||||||||||||||||||||
| 11 | 6 | 8/2/23 | 15/2/23 | No match found | |||||||||||||||||||||||||
| 12 | 7 | 26/1/23 | 2/2/23 | No match found | |||||||||||||||||||||||||
| 13 | 8 | 6/2/23 | 13/2/23 | No match found | |||||||||||||||||||||||||
| 14 | 9 | 2/2/23 | 9/2/23 | 13,11 | No match found | ||||||||||||||||||||||||
| 15 | 10 | 15/2/23 | 22/2/23 | No match found | |||||||||||||||||||||||||
| 16 | 11 | 14/2/23 | 21/2/23 | No match found | |||||||||||||||||||||||||
| 17 | 12 | 12/2/23 | 19/2/23 | 6 | No match found | ||||||||||||||||||||||||
| 18 | 13 | 1/2/23 | 8/2/23 | No match found | |||||||||||||||||||||||||
| 19 | 14 | 10/2/23 | 17/2/23 | No match found | |||||||||||||||||||||||||
| 20 | 15 | 11/2/23 | 18/2/23 | 6 | No match found | ||||||||||||||||||||||||
| 21 | 16 | 8/2/23 | 15/2/23 | No match found | |||||||||||||||||||||||||
Sheet1 | |||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| J6:J21 | J6 | =IFERROR(INDEX($H$6:$H$21, MATCH(SMALL(IFERROR((IF(FIND(",",I6), LEFT(I6,FIND(",",I6)-1), I6))=$F$6:$F$21, MATCH((IF(FIND(",",I6), LEFT(I6,FIND(",",I6)-1), I6)),$F$6:$F$21,0)), ROW(INDIRECT("1:1"))), $F$6:$F$21, 0)), "No match found") |
| Press CTRL+SHIFT+ENTER to enter array formulas. | ||
