A very common example of this is when you have names in two columns and you want to check if the names are exactly the same or what names are missing in one column. Comparing text in Excel is fairly easy and can be achieved with simple formulas. In this tutorial, I will show you how to compare text in Excel using simple arithmetic operators or the EXACT function. I will also cover how you can compare text in two columns to find out the missing text entries.
Compare Text in Excel (Exact Cell against Cell Comparison)
Below I have a data set where I have names in column A as well as column B, and I want to compare the names in each row. I want to check whether the names in the same row are the same or not. Let’s look at two simple ways to get this done.
Using the Equal to Operator
The equal operator can commit will pare the content in one cell with another cell, and give you a TRUE in case the cells have the exact same text in it, or FALSE in case the text is not the same. Below is the formula that will compare the text in two cells in the same row: Enter this formula in cell C3 and then copy and paste it into all the cells. The above formula returns a TRUE in case there is an exact match (meaning that the names are exactly the same), and it returns a FALSE in case the names do not match In our example above, you can see that the formula returns FALSE in cells C6 and C11, indicating that the names in row #6 and row #11 are not the same. If you want to only see the rows where the names are not the same, you can apply filters on the headers and then filter only those cells in column C where the value is FALSE Note: For the formula to return a TRUE, the cell contents need to be exactly the same. In case there is an extra space in one of the cells, while it may look as if the cells have the same name, the formula would return a FALSE. When you use the equal to operator, it does not consider the case of the text in the cell. For example, if you have ‘James Baker’ in one cell and ‘james baker’ in another cell and you compare these two, the formula would return a TRUE. In case you want the comparison to be case-sensitive, use the EXACT function method covered next.
Using the EXACT function
Another easy way to compare text in two cells in Excel is by using the EXACT function. As the name suggests, it would return TRUE in case the content of the two cells being compared is exactly the same, and FALSE in case the contents are not the same. Considering the same data set with names and column A and column B, below is the formula that will compare the names and give us the result Enter this formula in cell C3 and then copy and paste it into all the cells. The EXACT function takes the cell reference of the cells that need to be compared as the arguments and returns a TRUE in case there is an exact match, and a FALSE if there is not. In case you’re using newer versions of Excel that have dynamic arrays, you can also use the below formula in cell C2 (and you don’t need to copy and paste the formula in the remaining cells as the dynamic formula would spill over and fill the other cells automatically) Note that the EXACT function is case sensitive, so even if the names are exactly the same but in different cases (lower or upper, or proper), the formula would return a FALSE.
Compare Text and Find Missing Text Using VLOOKUP
Another real-life situation where you may need to compare text is when you have a list in two columns and you want to find out the items/names that are there in one column but missing in the other column. Below I have a data set where I have some names in column A and in column B, and I want to check what names in column A are also they’re in column B, and which ones are missing Below is the formula that would give us the text “Present” in case the name in column A is also there in column B and it would give us “Missing” in case the name in column A is not present in column B. Enter this formula in cell C3 and then copy and paste it into all the cells. The above formula uses the VLOOKUP function to check for each name in column A against the list in column B. If the VLOOKUP formula finds the name, it would return that name, and in case it doesn’t find the name, it would return the #N/A! error I haven’t wrapped this VLOOKUP formula inside the ISERROR function so that if the names are present, it would return a FALSE, and if the names are missing then it would return a TRUE. And then I wrapped this inside an IF function, which gives us the text ‘Present’ if the name in column A is also there in column B, as it returns the text ‘Missing’ You can also use a similar formula construct to check the other way around, whether the names in column B are present in column A or not (by adjusting the formula accordingly). In this example, I have used the VLOOKUP function to compare the text in two columns, but this can also be done with other formulas such as INDEX/MATCH or XLOOKUP.
Compare Text and Check If Partial Text Matches
Another common situation that I have come across is when people want to compare the text in one cell with another cell, but they’re not looking for an exact comparison, and just need to check whether the text in one cell is present in the other cell or not. Below I have a data set where I have some names in column A and in column B. You would notice that the names in column B are only the first names, and the names in column A are full names. Now I want to compare these two names and check whether the name in column B is there in column A or not. As you can see, I’m not looking for an exact comparison but a partial text match. And below is the formula that will do this for me: Enter this formula in cell C3 and then copy and paste it into all the cells. The above formula uses the FIND function to check whether the text value in column B is present in the cell in column A or not. If the FIND function finds the text in column A it would return the starting position of that text (i.e., it would return a numeric value), and if it cannot find the text, it would return the VALUE error. I have wrapped the FIND function inside the ISNUMBER function, which would return a TRUE in case the FIND function found the text in column A and returned a value, otherwise, it would return a FALSE. In this tutorial, I covered some simple formulas that you can use to quickly compare text in Excel. If you’re looking to compare one cell with another, you can use the equal-to operator or the EXACT function. And if you’re trying to compare two columns, you can use the VLOOKUP function. And finally, I’ve also covered a method to show you how to compare partial text using the find function. Other Excel tutorials you may also like:
How to Compare Two Excel Sheets (for differences) How to Compare Dates in Excel (Greater/Less Than, Mismatches) How to Compare Two Columns in Excel (for matches & differences) Separate Text and Numbers in Excel (4 Easy Ways) How To Remove Text Before Or After a Specific Character In Excel
