Find a cell value based on data in two columns of a table

Question

Using and Excel formula, I would like to complete last three columns of table A below based on data in table B. If any row of a particular ID in table B contain "Yes" in a year, then put "Yes" in table A on the col for same year. For example, ID=11 has "Yes" in one row in col=2015, in three rows in col=2016, and in no rows in col=2017. So put "Yes" in 2015 and 2016 columns in table A where ID=11

Table A

ID	2015	2016	2017
11
13
15

Table B

ID	2015	2016	2017
11		yes
11		yes
11	Yes	yes
13	yes
13	yes	yes
13
13		yes
15			Yes
15	yes
15	yes	yes
15	yes	yes

I tried,

=VLOOKUP(A2,$A$2:$D$12,2,FALSE)
=VLOOKUP(A2,$A$2:$D$12,3,FALSE)
=VLOOKUP(A2,$A$2:$D$12,4,FALSE)

Answer 1

Try SUMPRODUCT() with IF() function.

=IF(SUMPRODUCT(($H$2:$J$12="Yes")*($G$2:$G$12=$A2)*($H$1:$J$1=B$1))>0,"Yes","")