Hi all,
Since it is my first time using Excel in this manner, I am not sure if Excel is even able to do so, but I have to try because if it works, it will save me a lot of time.
I have got three columns (of which one in a different workbook, but I assume that will not be a problem)
Column A: a list of container numbers
Column B: the date that specific container is supposed to arrive in the harbour
Column C: A shipping notification which sometimes contains the container number
I want column D to show the arrival date of that container as shown in the data below.
<tbody>
</tbody>
The issue I am dealing with is the following:
* The Container number in the shipping notification can have extra numbers/letters behind and in front of the container number
I have come up with the following formula: =INDEX(B2:B130;MATCH(C2:C1729;A2:A130;0)) Which works....but only when the exact container number is placed as shipping notification. I was thinking of putting asterixes around the A column in the function, but either I am not doing it right (wrong place?) or it doesn't work that way.
While I am at it I want to ask another thing (of which I suspect it is not possible). Is it possible for the formula to not change column D if there is no match? So lets say for row 9 D was already entered as 9-8-2014, the desired outcome in the example would be 9-8-2014. If this is not possible I will create another column compare the two (the existing D and the new D).
Thanks for the effort!!
Tamara
ps: I'm Dutch, so it is correct that I use semicolumns instead of comma's, else it wont work.
Since it is my first time using Excel in this manner, I am not sure if Excel is even able to do so, but I have to try because if it works, it will save me a lot of time.
I have got three columns (of which one in a different workbook, but I assume that will not be a problem)
Column A: a list of container numbers
Column B: the date that specific container is supposed to arrive in the harbour
Column C: A shipping notification which sometimes contains the container number
I want column D to show the arrival date of that container as shown in the data below.
Row | Container numbers = Column A | Arrival date = Column B | Shipping notification =Column C | Desired outcome = Column D | Explanation | ||
1 |
<colgroup><col width="150"></colgroup><tbody> </tbody> | 24-7-2014 | CAN140171#33 | 21-7-2014 | CAN140171 in row 4 is the container number | ||
2 |
<colgroup><col width="150"></colgroup><tbody> </tbody> | 22-7-2014 |
<colgroup><col width="146"></colgroup><tbody> </tbody> | 21-7-2014 | CAN020593 in row 8 is the container number | ||
3 |
<colgroup><col width="150"></colgroup><tbody> </tbody> | 22-7-2014 |
<colgroup><col width="146"></colgroup><tbody> </tbody> | 21-7-2014 | 1NG036027 in row 11 is the container number | ||
4 |
<colgroup><col width="150"></colgroup><tbody> </tbody> | 21-7-2014 | 1HK863558 | 22-7-2014 | Equal to the container number in row 7 | ||
5 |
<colgroup><col width="150"></colgroup><tbody> </tbody> | 21-7-2014 | 00XMN182515 | 21-7-2014 | Ends with the container number in row 5 | ||
6 |
<colgroup><col width="150"></colgroup><tbody> </tbody> | 24-7-2014 | PKG040244#21 | 24-7-2014 | Begins with the container number in row 6 | ||
7 |
<colgroup><col width="150"></colgroup><tbody> </tbody> | 22-7-2014 | 1SL046965 | 24-7-2014 | Equal to the container number in row 9 | ||
8 |
<colgroup><col width="150"></colgroup><tbody> </tbody> | 21-7-2014 | 15514XMN182515 | 21-7-2014 | Ends with the container number in row 5 | ||
9 |
<colgroup><col width="150"></colgroup><tbody> </tbody> | 24-7-2014 | 1GQ486279 | Does not contain a container number | |||
10 |
<colgroup><col width="150"></colgroup><tbody> </tbody> | 24-7-2014 | XMN182515 | 21-7-2014 | Equal to the container number in row 5 | ||
11 |
<colgroup><col width="150"></colgroup><tbody> </tbody> | 21-7-2014 | 1SL046965 | 24-7-2014 | Equal to the container number in row 9 |
<tbody>
</tbody>
The issue I am dealing with is the following:
* The Container number in the shipping notification can have extra numbers/letters behind and in front of the container number
I have come up with the following formula: =INDEX(B2:B130;MATCH(C2:C1729;A2:A130;0)) Which works....but only when the exact container number is placed as shipping notification. I was thinking of putting asterixes around the A column in the function, but either I am not doing it right (wrong place?) or it doesn't work that way.
While I am at it I want to ask another thing (of which I suspect it is not possible). Is it possible for the formula to not change column D if there is no match? So lets say for row 9 D was already entered as 9-8-2014, the desired outcome in the example would be 9-8-2014. If this is not possible I will create another column compare the two (the existing D and the new D).
Thanks for the effort!!
Tamara
ps: I'm Dutch, so it is correct that I use semicolumns instead of comma's, else it wont work.