Create new Column: values based on column header (year) from another table

[adoster]

Hello,

I'm trying to create a new column with values from another table, based on an ID & the column header which is a date/year value.

Tables are linked via "Code"

Table A

Name	Code	Date
Adam	C	2/25/2019
Brock	A	8/3/2019
Caleb	C	7/6/2020
Josh	B	6/1/2020
John	D	4/5/2021
Sarah	A	5/15/2021
Andrew	E	12/12/2019

Table B

Code	2019	2020	2021
A	1	4	3
B	2	5	4
C	3	6	5
D	4	7	6
E	5	8	7

New Column Results

Name	Code	Date	Value
Adam	C	2/25/2019	3
Brock	A	8/3/2019	1
Caleb	C	7/6/2020	6
Josh	B	6/1/2020	5
John	D	4/5/2021	6
Sarah	A	5/15/2021	3
Andrew	E	12/12/2019	5

[ddpl · Accepted solution]

@adoster 

Create a calculated column...

Value = SWITCH(TRUE(),

                  FORMAT('Table A'[Date], "yyyy") = "2019", LOOKUPVALUE('Table B'[2019], 'Table B'[Code], 'Table A'[Code]),

                  FORMAT('Table A'[Date], "yyyy") = "2020", LOOKUPVALUE('Table B'[2020], 'Table B'[Code], 'Table A'[Code]),

                  FORMAT('Table A'[Date], "yyyy") = "2021", LOOKUPVALUE('Table B'[2021], 'Table B'[Code], 'Table A'[Code])

)

Hope it will work for you.

[adoster]

This works great! THANK YOU!