index match to result only expiring dates

[GeorgeDoors]

Hi, i've been trying for weeks to get an index match to result the the date and the manufacturer for when the object expires with no gaps.

Her is the initial table, i have shrunk this as the actual one is 88 company's by 36 manufacturers;

<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
[TD="align: center"]Manufacturer1[/TD]
[TD="align: center"]Manufacturer2[/TD]
[TD="align: center"]Manufacturer3[/TD]

[TD="align: center"]Company 1[/TD]
[TD="align: center"]1/2/2016[/TD]
[TD="align: center"]1/12/2016[/TD]
[TD="align: center"]-[/TD]

[TD="align: center"]Company 2[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]1/8/2016[/TD]
[TD="align: center"]1/1/2018[/TD]

[TD="align: center"]Company 3[/TD]
[TD="align: center"]1/11/2016[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]1/1/2017[/TD]

[TD="align: center"]Company 4[/TD]
[TD="align: center"]1/7/2016[/TD]
[TD="align: center"]1/6/2016[/TD]
[TD="align: center"]-[/TD]

</tbody>

I would like the formula to return the manufacturer and the date of expiry in the table below, the number of columns can increase, but the idea is to remove the gaps and show differing remaining terms across different tabs

<colgroup><col style="width: 100px"><col width="156"><col width="156"></colgroup><tbody>
[TD="align: center"]Company 1[/TD]
[TD="align: center"]Manufacturer1 01/02/2016[/TD]
[TD="align: center"]Manufacturer2 01/12/2016[/TD]

[TD="align: center"]Company 2[/TD]
[TD="align: center"]Manufacturer2 01/08/2016[/TD]
[TD="align: center"]Manufacturer3 01/01/2018[/TD]

[TD="align: center"]Company 3[/TD]
[TD="align: center"]Manufacturer1 01/11/2016[/TD]
[TD="align: center"]Manufacturer3 01/01/2017[/TD]

[TD="align: center"]Comapny 4[/TD]
[TD="align: center"]Manufacturer1 01/07/2016[/TD]
[TD="align: center"]Manufacturer2 01/06/2016
[/TD]

</tbody>

This result i typed out but would be too time consuming to do manually per month.

Any help will be much appreciated

Many Thanks

 

[GeorgeDoors]

I have relooked at this and i don't need it to include the date but just the manufacturer

 

[AhoyNC]

Maybe something like this.
This is an array formula and must be entered with CTRL-SHIFT-ENTER (command-return on MAC)
Formula in B9 of the example can just be copied down and across if you are careful on how you lock the cell references.

Excel Workbook
	A	B	C	D	E
1		Manufacturer1	Manufacturer2	Manufacturer3
2	Company 1	1/2/2016	1/12/2016	-
3	Company 2	-	1/8/2016	1/1/2018
4	Company 3	1/11/2016	-	1/1/2017
5	Company 4	1/7/2016	1/6/2016	-
6
7
8
9	Company 1	Manufacturer1	Manufacturer2
10	Company 2	Manufacturer2	Manufacturer3
11	Company 3	Manufacturer1	Manufacturer3
12	Company 4	Manufacturer1	Manufacturer2
Sheet

 

[GeorgeDoors]

Thank you very very much, it works perfectly.
Is it possible to make it date specific, ie if the terms expire in 3 months or less?

 

[AhoyNC]

This would return value for dates that are equal to or greater than today and less than or equal to 3 months from today.

Excel Workbook
	A	B	C	D
1		Manufacturer1	Manufacturer2	Manufacturer3
2	Company 1	1/2/2016	4/5/2016	-
3	Company 2	-	1/8/2016	7/8/2016
4	Company 3	3/4/2016	-	4/7/2016
5	Company 4	1/7/2016	2/29/2016	2/28/2016
6
7
8
9	Company 1	Manufacturer2
10	Company 2
11	Company 3	Manufacturer1	Manufacturer3
12	Company 4	Manufacturer2	Manufacturer3
Sheet