How to Compare Two Columns in Excel For Matches & Differences Using Formula

How to Compare Two Columns in Excel For Matches & Differences Using Formula
In this excel tutorial, I'll show you how you can compare two columns in excel for matches & differences using Formula based on the strings or value.

There are several ways you can compare two columns in excel, but using the exact Function in excel is the easiest them all. We'll also be able to use this technique to compare two lists to find out if they are an exact match or not. It will also allow us to see if they are case sensitive or not.
Let's learn how to compare two columns based on values. I'm going to show you two Formula to compare two cells or columns. Let's go for the first one:

=EXACT(A1, B1)

The Function above is the use of Exact Function. By this Function, Excel will compare cell A1 & B1 to figure out if they are an exact match. It will also look for case sensitivity. If the cell A1 has a value in All uppercase and if the cell B1 has the same value all in lowercase, it will say false because the case didn't match. So, using this Formula, we can tell excel to compare two columns for matches or Differences. This is a great way to compare 2 columns in excel
Now, the second Formula is a simple one.

=A1=B1

This Formula will make the same comparison except for case sensitivity. For example: If the cell A1 has a value in All uppercase and if the cell B1 has the same value all in lowercase, it will say TRUE because the case doesn't matter here. It just focuses on the text strings. So, you can use this Formula to compare text strings in excel.

So, this is how you can compare two columns excel. These two are the easiest and effective ways for column comparison in excel. Thanks for reading.

#Formula #Compare #Columns

Thanks for watching.
-------------------------------------------------------------------------------------------------------------
Support the channel with as low as $5
-------------------------------------------------------------------------------------------------------------
Please subscribe to #excel10tutorial

Playlists:

Social media:

[keomapedro]

I looked at 4 videos prior to yours & their's were very complicated. You wouldn't realize how much help this video was for after just the simple "=EXACT". Thanks again.

[ЕленаЯкимович-эй]

Thank you. Simple and quick. I needed something really quick and it was helpful.

[dawnmueller]

Thanks! Two quick lessons, one without case sensitivity. Great!!

[nhyiraba]

Very useful. Very helpful. Exactly what I was looking for. Thanks.🙏🏽

[hussain]

Thanks. All I need in few minutes. Good job.

[prathmeshyadav]

Thanks! Great video. As I wanted this specific command

[roopanaidu]

Thanx I was searching for this video but I couldn't get it in any other videos . Formula was simple and effective.

[cinematicvideobysimran]

So helpful video for me thanks brother ❤

[jon]

Hi I am having a difficult time using true and false. I am trying to match from two different worksheets and they are exactly the same but when I use true and false formula it says false when it’s correct. I am not matching numbers but words do the words have to be in a certain format?

[tomlangdale-hunt]

Is there any reason why this formula won't work for other rows when I try and drag the result down? Only seems to calculate the initial row I selected

[mailmrmanoj]

Can I give colour in formula to show green if true and red if false?

[ВиталийОвчаренко-тй]

To compare two columns in Excel for matches and differences using a formula, you can use the VLOOKUP or INDEX/MATCH functions. Here's a step-by-step guide for both methods:

Method 1: Using VLOOKUP

1. First, ensure that both columns have no blank cells or inconsistent data formatting.
2. Select a cell where you want to display the comparison result (e.g., cell C2).
3. Type the formula "=VLOOKUP(A2, B:B, 1, FALSE)" (without quotes), assuming column A has the values you want to compare with column B.
4. Press Enter to see the first match from column B in cell C2.
5. To find the next match, copy the formula in cell C2 and paste it in the cells below, adjusting the cell references accordingly (e.g., C3 for the next comparison).
6. To find differences, you can use the IF statement in combination with the VLOOKUP formula. In cell C2, type the formula "=IF(VLOOKUP(A2, B:B, 1, FALSE) = A2, "Match", "No Match")" (without quotes).
7. Copy and paste the formula in cells below to check for differences in other rows.

Method 2: Using INDEX/MATCH

1. First, ensure that both columns have no blank cells or inconsistent data formatting.
2. Select a cell where you want to display the comparison result (e.g., cell C2).
3. Type the formula "=IF(INDEX(B:B, MATCH(A2, A:A, 0)) = A2, "Match", "No Match")" (without quotes), assuming column A has the values you want to compare with column B.
4. Press Enter to see the first match or difference in cell C2.
5. To find the next match or difference, copy the formula in cell C2 and paste it in the cells below, adjusting the cell references accordingly (e.g., C3 for the next comparison).

These methods will help you compare two columns in Excel for matches and differences using formulas.