Index Match?

[manzier]

Hi All,

I have a matrix that I need to update based on a table I have. Essentially, if the table shows completion, I'll need the matrix to be updated with the corresponding date of completion

Example, I have this table:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Training[/TD]
[TD]Completion[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]Training1[/TD]
[TD]3/13/2016[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]Training4[/TD]
[TD]5/25/2016[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]Training2[/TD]
[TD]3/13/2016[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]Training4[/TD]
[TD]4/15/2017[/TD]
[/TR]
[TR]
[TD]Name3[/TD]
[TD]Training3[/TD]
[TD]6/18/2015[/TD]
[/TR]
[TR]
[TD]Name4[/TD]
[TD]Training1[/TD]
[TD]5/15/2016[/TD]
[/TR]
</tbody>[/TABLE]

I'll need to create a formula in the matrix below so that the matrix is updated as the table above is updated, please see below

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Training1[/TD]
[TD]Training2[/TD]
[TD]Training3[/TD]
[TD]Training4[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]3/13/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD]5/25/2016[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD][/TD]
[TD]3/13/2016[/TD]
[TD][/TD]
[TD]4/15/2017[/TD]
[/TR]
[TR]
[TD]Name3[/TD]
[TD][/TD]
[TD][/TD]
[TD]6/18/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name4[/TD]
[TD]5/15/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Is index match the proper way to go? Please help. Thanks in advance!

 

[AhoyNC]

If your dates are actual dates and not text then you could use SUMPRODUCT.
Copy formula down and across as needed.

Excel Workbook
	A	B	C	D	E
1	Name	Training	Completion
2	Name1	Training1	3/13/2016
3	Name1	Training4	5/25/2016
4	Name2	Training2	3/13/2016
5	Name2	Training4	4/15/2017
6	Name3	Training3	6/18/2015
7	Name4	Training1	5/15/2016
8
9
10	Name	Training1	Training2	Training3	Training4
11	Name1	3/13/2016			5/25/2016
12	Name2		3/13/2016		4/15/2017
13	Name3			6/18/2015
14	Name4	5/15/2016
Sheet

 

[Mike Szczesny]

Can you use this formula? I also enclosed the =iferror(......" ") to remove the error messages where there should not be any answer. The formula is [TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl65, width: 68"] =IFERROR(INDEX($C$2:$C$7,MATCH($A10&B$9,$A$2:$A$7&$B$2:$B$7,0))," ")

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 348"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Training[/TD]
[TD]Completion[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]Training1[/TD]
[TD="align: right"]3/13/2016[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]Training4[/TD]
[TD="align: right"]5/25/2016[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]Training2[/TD]
[TD="align: right"]3/13/2016[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]Training4[/TD]
[TD="align: right"]4/15/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name3[/TD]
[TD]Training3[/TD]
[TD="align: right"]6/18/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name4[/TD]
[TD]Training1[/TD]
[TD="align: right"]5/15/2016[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Training1[/TD]
[TD]Training2[/TD]
[TD]Training3[/TD]
[TD]Training4[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD="align: right"]3/13/2016[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]5/25/2016[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD] [/TD]
[TD="align: right"]3/13/2016[/TD]
[TD] [/TD]
[TD="align: right"]4/15/2017[/TD]
[/TR]
[TR]
[TD]Name3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]6/18/2015[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Name4[/TD]
[TD="align: right"]5/15/2016[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[johnmpl]

Hi, to all!

Another option could be:

Book1
	A	B	C	D	E
1	Name	Training	Completion
2	Name1	Training1	3/13/2016
3	Name1	Training4	5/25/2016
4	Name2	Training2	3/13/2016
5	Name2	Training4	4/15/2017
6	Name3	Training3	6/18/2015
7	Name4	Training1	5/15/2016
8
9	Name	Training1	Training2	Training3	Training4
10	Name1	3/13/2016			5/25/2016
11	Name2		3/13/2016		4/15/2017
12	Name3			6/18/2015
13	Name4	5/15/2016
Hoja1

Cell Formulas
Range	Formula
B10	=IFERROR(1/(1/SUMIFS($C$2:$C$7,$A$2:$A$7,$A10,$B$2:$B$7,B$9)),"")

Blessings!