As a work example, we now have two tables, which are the employee's January salary scale and the February salary scale
The requirement now is to merge the two tables into one, as in the style on the right
If you copy and paste manually, the efficiency will be too slow
Two workarounds are shared today
1. Official Law
First, we use the formula to extract the unique values of column A and column D, using the formula:
=UNIQUE(VSTACK(A1:A7,D1:D6))
VSTACK is to arrange two columns of data into one column
UNQIUE deduplicates the data regions and obtains the following unique data
Then we use the lookup matching formula XLOOKUP to match the data separately
=XLOOKUP(G2,A:A,B:B,"")
The search value is cell G2, the search column is column A, and the result column is column B
In the same way, we enter the formula in the February salary:
=XLOOKUP(G2,D:D,E:E,"")
2. Combined calculation method
If we are not very familiar with the formula, we can use the trick method, which can be combined with just a few clicks
Let's select the G1 cell where the data is stored
Then click on the Data tab, click on the Data tool, and select Combine Calculations as shown below:
Then we select the data region in the reference location and add the data separately
Then label the position and check the first row and the leftmost column
After clicking OK, the result is as follows
Which of the above 2 methods do you prefer? Try it out!