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

Here, an array version that spill all the result at once including header. In cell F1 put the following formula:

=LET(id, A2:A12, data, B2:D12, ux, UNIQUE(id), yr, B1:D1,
 out, IF(MMULT(TRANSPOSE(N(id = TOROW(ux))), N(data="yes")) > 0, "yes", ""),
 VSTACK(HSTACK("", yr), HSTACK(ux, out))
)

LET is used for easy reading and composition, here the formula without using it:

=VSTACK(HSTACK("", B1:D1), HSTACK(UNIQUE(A2:A12), IF(MMULT(TRANSPOSE(N(A2:A12 
  = TOROW(UNIQUE(A2:A12)))), N(B2:D12="yes")) > 0, "yes", "")))

It uses MMULT to do the counts by ID and year (out), the rest is just to accommodate the output via VSTACK/HSTACK.

Answer 2

If you want to use VLOOKUP then it will require following approach.

Formula in highlighted cell which can be copied down and across(may need CTRL+SHIFT+ENTER in versions other than O365)

=IF(ISNA(VLOOKUP($A2&"yes",$G$2:$G$12&H$2:H$12,1,0)),"","Yes")

Answer 3

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","")

Answer 4

VLOOKUP looks only for first value so in your data it will often be empty. You can use COUNTIFS.

Formula in G2 (and dragged to I4):

=IF(COUNTIFS($A:$A,$F2,B:B,"yes")>0,"yes","")

Result: