silikonsmooth.blogg.se - Formula to compare two columns in excel

The EXACT function looks for two values and returns “TRUE” if the value 1 is equal to value 2. This function is a logical function, so it provides true or false as a result. If we want to match or compare two columns with a case sensitive approach, we need to use the Exact function in excel Exact Function In Excel The exact function is a logical function in excel used to compare two strings or data with each other, and it gives us the result whether the both data are an exact match or not. It has eliminated the trailing space in cell B9. We got the result as “TRUE” because we have applied a TRIM function. Now, let us look at the result in cell C9. Below is the way of using the TRIM function. To overcome these kinds of scenarios, we can apply the formula with the TRIM function, which removes all the unwanted space characters.

As we can see, one trailing space character entered after the word “Delhi” contributes as an extra character.

So then, we must press the F2 key (edit) in cell B9.

In cell A9, we have 9 characters, but in cell B9, we have ten characters, i.e., one extra character.

Let us apply the LEN function in Excel for each cell, which shows the number of characters in the selected cell.By looking at the data, we cannot tell what the difference is we need to go into minute analysis mode. In cells A9 and B9, we have the same value as “New Delhi,” but still, we got the result as “FALSE.” It is an extreme case but a real-time example. The result is “FALSE.”Ĭell C9: This is a special case.

In some cells, we received the result as “FALSE”(colored cells), which means “Source 1” data is not equal to “Source 2.” Let us look at each cell in detail.Ĭell C3: In the A3 cell, we have “New York,” and in the B3 cell, we have “New York.” Here, we do not have space characters after the word “New.” So, the result is “FALSE.”Ĭell C7: In the A7 cell, we have “Bangalore,” and in cell B7, we have “Bengaluru.” So both are different. Next, we must drag the formula to the remaining cells to get the result.If “Source 1” is equal to “Source 2,” we can get the result as “TRUE” or else “FALSE.” Since we match Source 1 = Source 2, let us select the formula as A2 = B2.First, we need to open the equal sign in the C2 cell. We need to match whether “Source 1” data is equal to “Source 2” or not. We have city names from two different sources.