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)
CodePudding user response：
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","")
CodePudding user response：
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:
CodePudding user response：
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")