Advanced paste in excel using same cell ID

Question

I have 2 separate spreadsheets which I would like to perform an advanced paste function on. The tables contain ~4000 records relating to a Value 1 and date. We would like to query dates in the last year with value over 100. We want to use the excel filter tab for this. However, as the data is in 2 tables pasting to a single table is difficult as some IDs are missing example below.

Table 1

ID	Value
1	100
2	200
3	100
5	100
7	200
8	100

Table 2

ID	Date
1	2020
2	2010
3	2020
4	2010
6	2010
8	2020

Is it possible to amalgamate the 2 above tables in 1 table based upon ID. We would like the output to look something like this, thanks in advance

Table 3

ID	Value	Date
1	100	2020
2	200	2010
3	100	2020
4		2010
5	100
6		2010
7	200
8	100	2020

Answer 1

You can achieve it by INDEX/MATCH formula. try-

C12=IFERROR(INDEX($B$2:$B$7,MATCH(B12,$A$2:$A$7,0)),"")
D12=IFERROR(INDEX($E$2:$E$7,MATCH(B12,$D$2:$D$7,0)),"")

With Excel-365 you can simplify formula-

=XLOOKUP(B12,$A$2:$A$7,$B$2:$B$7,"")