excelexcel-formulapasteexcel-tablescustom-functions-excel

Advanced paste in excel using same cell ID

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
Solution

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

    enter image description here