Vlookup, Index and Match

Copper Contributor

I have two tabs called "table" and "data". I need to find the dates when employees were last trained in each topic. On tab "data" there's staffs name on Column A, topic in Column B, Trained date on Column C. On tav "table", I have Staff name on Column A, and topics from B1 to F1. How can I find the date each employee was trained in each topic without having to manually type their dates? Is it possible to use any formula? Thank You in Advance.

 

2.png1.png

3 Replies

@deeps470 

In C2 on the data sheet:

 

=MAXIFS(INDEX(table!$B:$F,0,MATCH(B2,table!$B$1:$F$1,0)),table!$A:$A,A2)

 

Fill down.

@HansVogelaar Hi Hans, Thank You for your help. However, i require formula to be put on B2 by comparing two coloums in "Name and Course Title to get the trained date in. 

@deeps470 Sorry, I interpreted your question the other way round.

 

In B2 on the table sheet:

 

=MAXIFS(data!$C$2:$C$1000, data!$A$2:$A$1000, $A2, data!$B$2"$B$1000, B$1)

 

Fill to the right to F2, then fill down.