Practical Excel skills sharing: three commonly used methods for checking data

I think those who often use Excel will encounter the problem of checking data. How do you usually do it? Today I will share with you the three most commonly used methods for checking data, come and take a look!

#How to check the difference between two columns of data is a problem often encountered by partners who use Excel. As shown in the figure below, the left column is all the order numbers, and the right column is the order numbers that have been shipped. You need to determine whether there is a difference between the order numbers in the two columns.

This type of problem sounds simple and can be solved using the VLOOKUP function or COUNTIF function, but the actual situation is more complicated. The two columns of data, which is the comparison value (search value or condition value) and which is the reference value (search range or counting area), directly affect the actual meaning contained in the function result.

Today the veteran will sort out these problems. I hope that friends can solve them clearly when they encounter data verification problems in the future.

1. Use the VLOOKUP function to check the data

##1. Column C is used as the comparison value (lookup value)

Formula

=VLOOKUP(C2,A:A,1,0)You can get the result shown in the figure below:

In this In the formula, the search value (the first parameter, that is, the comparison value) is the order number that has been shipped in column C, and the search range (the second parameter, that is, the reference value) is all the order numbers in column A.

The results will appear in two situations:

(1) Get an order number (in column A). This situation indicates that the search value (the order number in column C) is in the search range (A There is corresponding data in the column). In this example, it means that the order number that has been shipped is within the range of all order numbers;

(2) gets an error value (#N/A), which means that the search value (column C The order number) has no corresponding data in the search range (column A). In this case, the order number indicating that it has been shipped is not in the range of all order numbers and is the order number in question. It is necessary to check whether the order number record is incorrect or whether it belongs to another organization.

2. Column A is used as a comparison value (lookup value)

Formula

=VLOOKUP(A2,C:C,1,0) Yes The result shown in the figure below is obtained:

The difference between Formula 2 and Formula 1 is that the positions of the search value and search range are exchanged, that is, column A is searched in column C. The corresponding order numbers in , the results are the same two situations, but the meaning has changed.

If you can get the order number, it means that the order has been shipped (because there is corresponding data in column C); if you get an error value, it means that the order has not been shipped, and you need to follow up on subsequent business operations. .

By comparing the results of Formula 1 and Formula 2, you need to understand that when the comparison value and the reference value change, the meaning of the result is completely different. Therefore, before checking the data, you must first figure out what problem needs to be solved, and then you can determine what to use as a comparison value and what to use as a reference value.

Extended application:

If you want to display the error value as information that needs to be prompted, you can use the IFERROR function to cooperate. For example, formula 1 can be modified as:

=IFERROR(VLOOKUP(C2,A:A,1,0),"The order number is incorrect and needs to be verified"), and formula 2 can be modified as: =IFERROR (VLOOKUP(A2,C:C,1,0),"Not Shipped"), the result is as shown in the figure:

If you want to be more direct To show whether it has been shipped and whether the data is normal, you can use the IF function and the ISERROR function. Formula 2 can be modified as:

=IF(ISERROR(VLOOKUP(A2,C:C,1,0)),"Not Sent Goods", "shipped"), Formula 1 can be modified as: =IF(ISERROR(VLOOKUP(C2,A:A,1,0)),"The order number is incorrect and needs to be verified", "Normal"), the result is as shown in the figure:

In fact, when checking the difference between two columns of data, you can also use the COUNTIF and MATCH functions, as follows Briefly explain the method of checking using these two functions.

2. Use COUNTIF to check two columns of data

COUNTIF is a conditional counting function that requires two parameters in the format of COUNTIF (Counting Area , condition value). In this example, there are still two formulas, namely:

=COUNTIF(A:A,C2) and =COUNTIF(C:C,A2), the results are as shown in the figure Show.

There are also two results obtained by COUNTIF, 0 and a number greater than 0, indicating the number of times the condition value (the second parameter, that is, the comparison value) appears in the counting area (the first parameter, that is, the reference value). A result of 0 indicates that it has not occurred, which has the same meaning as VLOOKUP's #N/A.

If you want to add prompt information, you can combine it with the IF function. The corresponding formula is: =IF(COUNTIF(A:A,C2)=0,"The order number is incorrect and needs to be verified"," Normal") and =IF(COUNTIF(C:C,A2)=0,"Not shipped","Shipped"), the result is as shown in the figure.

3. Use MATCH to check two columns of data

The function of the MATCH function is to get the search value in Find the location number in the area. The function requires three parameters, in the format: MATCH (search value, search area, search method). In this example, the two formulas are: =MATCH(C2,A:A,0) and =MATCH(A2,C:C,0), the result is as shown in the figure.

The result of the MATCH function is similar to that of VLOOKUP. The difference is that VLOOKUP gets the corresponding content (order number), while MATCH gets the line number where the order number is located.

Personally, I think it is more convenient to use the MATCH function when checking two columns of data. Not only can you compare the differences between the two columns of data, but you can also get the specific location of the result, which is very important at some times.

The method of combining MATCH function with IFERROR is exactly the same as VLOOKUP, so I won’t give an example.

In fact, MATCH is a very useful function. If you like this tutorial, please remember to support us. We will introduce more examples to you later!

Related learning recommendations: excel tutorial

The above is the detailed content of Practical Excel skills sharing: three commonly used methods for checking data. For more information, please follow other related articles on the PHP Chinese website!