How to compare multiple columns of data! When it comes to comparing multiple columns of data, it’s actually not difficult to say, nor easy to say. Before learning, I need to introduce you to a new friend, VLOOKUP, so let’s take a look together!
In the last study, we learned that we can use the merge calculation function to compare single column data. We compared names based on job numbers to find out the changes in personnel. Today we are going to compare four columns of data: basic salary, job salary, social security, and provident fund. It is a comparison of multiple columns of data.
##Last month’s data #We can also use merged calculations to compare multiple columns of data. Please think about and experiment with how to merge and compare data. What the editor here wants to share with you is another Super 6 method, which can quickly compare the differences between data! That's right, that's it - the VLOOKUP function! It is the most popular function in Excel~VLOOKUP is a search function. Its main function is to return the value at the intersection of the specified column in the search area and the row where the value is being found. Function structure:
VLOOKUP（查找啥，在哪查，返回第几列，0）
##What to look for
: That is the value to be found~Where isSearch
: That is, the area to be searched ~Which column to return
: That is, which column to return the data in the search area ~Exact search/Approximate search
: Generally we search exactly, the default value is 0; if it is an approximate search, the default value is 1After reading the above introduction, are you a little confused? Don’t worry, you will all understand with an example!
There are two tables. The first table only has the work number but no name, while the second table is complete and contains both the work number and the name. We want to use the data in Table 2 to fill in the name column in Table 1. In other words, the job number is searched in Table 2, and then the name corresponding to the job number is returned to Table 1.
The formula should be like this:
=VLOOKUP(B4,E$4:F$9,2,0)
② Where to search: We need to search in the E4:F9 area of Table 2. At the same time, in order to keep the search area unchanged when the formula is filled down, we must add an absolute reference symbol to lock the number of rows, so the search area is E$4:F$9
③ Which column to return: We need to return the name column in Table 2, and the name column is the second column in the E:F area, so it is the number 2④ 0: Here we To achieve accurate search, the default value is 0After looking at the above examples, I believe that my friends have begun to understand it. Let’s strike while the iron is hot and get back to the topic! We need to simultaneously check the changes in basic salary, job salary, social security, and provident fund data last month and this month.
(1) Enter the following formula in this month’s I2:
=C2-VLOOKUP($A2, basic salary data last month!$A:$F,3, 0)
① What to look for: We need to find the employee number. The first employee number cell is A2. At the same time, in order to prevent the formula from changing when the formula is pulled to the right and filled in, an absolute reference needs to be added to lock column A, so it is $A2
② Where to search: We need to search for basic salary, provident fund, etc. in the A:F area of last month’s data. Also, in order to prevent the right pull-down filling formula from changing, an absolute reference symbol must be added, so it is “Salary basic data last month!$” A:$F”
③ Which column is returned: The basic salary is in the third column of A:F, so enter the number 3
④ 0: means precise search
(2) Copy cell I2 and fill it into J2:L2; then modify the third parameter of the formula in J2, K2, and L2 respectively, and change it to 4, 5, and 6 in sequence; finally select I2:L2 and add it to cell L2 Double-click in the lower right corner to fill in the formula downwards to complete the data comparison. The results are as follows.
If the difference is equal to 0, it means that the data of the previous month is consistent with this month's data; if the difference is positive, it means that the data of this month has increased; if the difference is negative, it means that the data of this month has declined.
If #N/A occurs, it means that the employee's data was not found in the previous month's data table, which means that the employee is a new employee this month.
How about it? Isn't it very simple? We completed the comparison of four columns of data through a formula. Hurry up and get your hands dirty!
Related learning recommendations: excel tutorial
The above is the detailed content of Practical Excel skills sharing: Use Vlookup to compare multiple columns of data. For more information, please follow other related articles on the PHP Chinese website!