INDEX MATCH formula in combination with a wildcard

[Tamarad]

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.

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Row
[/TD]
[TD]Container numbers
= Column A
[/TD]
[TD]Arrival date
= Column B
[/TD]
[TD]Shipping notification
=Column C
[/TD]
[TD]Desired outcome
= Column D
[/TD]
[TD]Explanation
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][TABLE="width: 150"]
<colgroup><col width="150"></colgroup><tbody>[TR]
[TD="width: 150"]1SL046965
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]24-7-2014
[/TD]
[TD]CAN140171#33
[/TD]
[TD]21-7-2014
[/TD]
[TD]CAN140171 in row 4 is the container number
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][TABLE="width: 150"]
<colgroup><col width="150"></colgroup><tbody>[TR]
[TD="width: 150"]1SL049225[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]22-7-2014
[/TD]
[TD][TABLE="width: 146"]
<colgroup><col width="146"></colgroup><tbody>[TR]
[TD="width: 146"]MSKU8021216-CAN020593
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]21-7-2014
[/TD]
[TD]CAN020593 in row 8 is the container number
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][TABLE="width: 150"]
<colgroup><col width="150"></colgroup><tbody>[TR]
[TD="width: 150"]1TP175537[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]22-7-2014
[/TD]
[TD][TABLE="width: 146"]
<colgroup><col width="146"></colgroup><tbody>[TR]
[TD="width: 146"]1NG036027DHLETS04.07[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]21-7-2014
[/TD]
[TD]1NG036027 in row 11 is the container number
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][TABLE="width: 150"]
<colgroup><col width="150"></colgroup><tbody>[TR]
[TD="width: 150"]CAN140171
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]21-7-2014
[/TD]
[TD]1HK863558
[/TD]
[TD]22-7-2014
[/TD]
[TD]Equal to the container number in row 7
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][TABLE="width: 150"]
<colgroup><col width="150"></colgroup><tbody>[TR]
[TD="width: 150"]XMN182515
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]21-7-2014
[/TD]
[TD]00XMN182515
[/TD]
[TD]21-7-2014
[/TD]
[TD]Ends with the container number in row 5
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][TABLE="width: 150"]
<colgroup><col width="150"></colgroup><tbody>[TR]
[TD="class: xl65, width: 150"]PKG040244
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]24-7-2014
[/TD]
[TD]PKG040244#21
[/TD]
[TD]24-7-2014
[/TD]
[TD]Begins with the container number in row 6
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][TABLE="width: 150"]
<colgroup><col width="150"></colgroup><tbody>[TR]
[TD="width: 150"]1HK863558
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]22-7-2014
[/TD]
[TD]1SL046965
[/TD]
[TD]24-7-2014
[/TD]
[TD]Equal to the container number in row 9
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD][TABLE="width: 150"]
<colgroup><col width="150"></colgroup><tbody>[TR]
[TD="width: 150"]CAN020593
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]21-7-2014
[/TD]
[TD]15514XMN182515
[/TD]
[TD]21-7-2014
[/TD]
[TD]Ends with the container number in row 5
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD][TABLE="width: 150"]
<colgroup><col width="150"></colgroup><tbody>[TR]
[TD="width: 150"]1SL046965
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]24-7-2014
[/TD]
[TD]1GQ486279
[/TD]
[TD][/TD]
[TD]Does not contain a container number
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD][TABLE="width: 150"]
<colgroup><col width="150"></colgroup><tbody>[TR]
[TD="class: xl65, width: 150"]1NG032517
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]24-7-2014
[/TD]
[TD]XMN182515
[/TD]
[TD]21-7-2014
[/TD]
[TD]Equal to the container number in row 5
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD][TABLE="width: 150"]
<colgroup><col width="150"></colgroup><tbody>[TR]
[TD="class: xl65, width: 150"]1NG036027
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]21-7-2014
[/TD]
[TD]1SL046965
[/TD]
[TD]24-7-2014
[/TD]
[TD]Equal to the container number in row 9
[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[Tamarad]

Maybe I need to add that the Container numbers don't always exist out of 3 letters and 6 numbers. This can be all number or any combination.....

 

[Andrew Poulsom]

Formula in D2 copied down:

Excel 2010
	A	B	C	D
1	Container numbers	Arrival date	Shipping notification	Desired outcome
2	1SL046965	24/07/2014	CAN140171#33	21/07/2014
3	1SL049225	22/07/2014	MSKU8021216-CAN020593	21/07/2014
4	1TP175537	22/07/2014	1NG036027DHLETS04.07	21/07/2014
5	CAN140171	21/07/2014	1HK863558	22/07/2014
6	XMN182515	21/07/2014	00XMN182515	21/07/2014
7	PKG040244	24/07/2014	PKG040244#21	24/07/2014
8	1HK863558	22/07/2014	1SL046965	24/07/2014
9	CAN020593	21/07/2014	15514XMN182515	21/07/2014
10	1SL046965	24/07/2014	1GQ486279	#N/A
11	1NG032517	24/07/2014	XMN182515	21/07/2014
12	1NG036027	21/07/2014	1SL046965	24/07/2014
Sheet1

Cell Formulas
Range	Formula
D2	=INDEX(B$2:B$12,MATCH(1,INDEX((--ISNUMBER(SEARCH(A$2:A$12,C2))),),FALSE))

 

[Tamarad]

Thanks that got me further that I was before. It works....but partially. When I use the format as in the example there are no problems, however when I put it in document A and get the first two columns out of document B (Caslijst) it will show some good ones, and some odd ones.
=INDEX('[Caslijst 21-07-2014.xls]Report'!$J$73:$J$135;MATCH(1;INDEX((--ISNUMBER(SEARCH('[Caslijst 21-07-2014.xls]Report'!$I$7:$I$135;O2704)));FALSE))

Gives a REF error for some of the data. There isn't one thing I can find that all those errors have in common. Do you have any ideas?

ps: due to Dutch formatting an automatic wink appeared in the formula, this ofcourse had to be O2707))); );FALSE)) but than without the space

 

[Andrew Poulsom]

You have $J$73:$J$135, but $I$7:$I$135. That might cause a #REF! error.

 

[Tamarad]

Somehow that does fix the #REF! error, but for the data that came back correct, gives a #N/A error......

 

[Tamarad]

No, skip that.....I changed it the wrong way. Absolutely nothing wrong with the formula now! It works perfectly. Thank you for the effort!!

 

[Tamarad]

I kinda have a follow up question to the previous one. I tried to use the formula to check another column both in Document A as in Document B.

The first one, where column X gets compared with column E (I still want the outcome of column J) only gave #NA!
=INDEX('[Caslijst 21-07-2014.xls]Report'!$J$7:$J$135;MATCH(1;INDEX((--ISNUMBER(SEARCH('[Caslijst 21-07-2014.xls]Report'!$E$7:$E$135;X2703))); );FALSE))

The second one, where column X gets compared with column E and column I only gave #NA!
=INDEX('[Caslijst 21-07-2014.xls]Report'!$J$7:$J$135;MATCH(1;INDEX((--ISNUMBER(SEARCH('[Caslijst 21-07-2014.xls]Report'!$E$7:$E$135;'[Caslijst 21-07-2014.xls]Report'!$I$7:$I$135;X2703))); );FALSE))

Can you please explain the formula for me, maybe than I know where I'm going wrong. I am not sure what the 1 in the MATCH formula does, nor what the --ISNUMBER does. Can you try to explain in laymen terms since my Excel knowledge only goes that far.

 

[Andrew Poulsom]

You can use the Evaluate Formula tool to see how it works.