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.

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

 

[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.