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:

Name	Training	Completion
Name1	Training1	3/13/2016
Name1	Training4	5/25/2016
Name2	Training2	3/13/2016
Name2	Training4	4/15/2017
Name3	Training3	6/18/2015
Name4	Training1	5/15/2016

<tbody>
</tbody>

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

	Training1	Training2	Training3	Training4
Name1	3/13/2016			5/25/2016
Name2		3/13/2016		4/15/2017
Name3			6/18/2015
Name4	5/15/2016

<tbody>
</tbody>

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

=IFERROR(INDEX($C$2:$C$7,MATCH($A10&B$9,$A$2:$A$7&$B$2:$B$7,0))," ")

<tbody>
</tbody>

Name	Training	Completion
Name1	Training1	3/13/2016
Name1	Training4	5/25/2016
Name2	Training2	3/13/2016
Name2	Training4	4/15/2017
Name3	Training3	6/18/2015
Name4	Training1	5/15/2016
Name	Training1	Training2	Training3	Training4
Name1	3/13/2016			5/25/2016
Name2		3/13/2016		4/15/2017
Name3			6/18/2015
Name4	5/15/2016

<colgroup><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

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