## What Is Compare And Match Two Columns In Excel?

Compare and Match Two Columns in Excelmeans that depending on the data structure, a user wants to compare the data and match them according to some conditions or scenarios to get logical results such asTRUEorFALSE.

There is no inbuilt formula to **Compare and Match. **However, we can use a number of functions in Excel, such as** IF(), VLOOKUP(), **etc., to perform the comparison and match.

For example, the image below depicts two sets of values, and we will **Compare and Match** two Columns in Excel by building a formula.

Select cell **C2**, enter the formula ** =A2=B2, **press the “

**Enter**” key, and drag the formula from cell

**C2 to C3**using the fill handle.

The output is shown above.

##### Table of contents

###### Key Takeaways

**Compare and Match Columns in Excel**help users compare two values, check if there is a match between the same, and returns True or False if the match is found.- Once we get the final result, we can use the
**Conditional formatting**feature to highlight the required data or to differentiate the results from the fed output. We can apply specific formatting to cells according to certain criteria. - Other techniques used to compare Excel columns are,
- The equals operator
**“=”.**

- The row-by-row comparison.

- The equals operator
- We can combine two functions, such as
**IF-AND, IF-OR**, or use other inbuilt functions, such as**VLOOKUP, EXACT**, etc, to**Compare and Match.**

### How To Compare Two Columns In Excel For Match?

We can **Compare and Match **data or columns in various ways, such as,

**Basic Way.****Using inbuilt Excel functions.**

#### Method #1 – Basic Way

The steps are:

- Type
**“=” equal to**sign. - Enter the first cell value or reference.
- Then, enter any operators such as
**=, >, <,**etc. - Enter the second cell value or reference.
- Press the “
**Enter**” key.

#### Method #2 – Using Inbuilt Excel Functions

We can use,

- The inbuilt functions such as
**IF excel function, VLOOKUP,****EXACT excel function, etc.** - Conditional formatting to highlight the compared and matched values.

### Examples

We will consider some advanced scenarios to **Compare and Match**, namely,

**Compare Two Columns of Data.****Case Sensitive Match.****Change Default Result TRUE or FALSE with IF Condition.****Highlight Matching Data.**

#### Example #1 – Compare Two Columns of Data

The following example depicts two sets of values, and we will **Compare and Match** Two Columns in Excel using the formula.

In the table, the data is,

- Column A contains Value 1.
- Here, column B contains Value 2.
- Column C contains the Match Output.

The steps to **Compare and Match Two Columns** are as follows:

**1:**Select cell**C2**, and enter the formula*=A2=B2.*

**2:**Press the “**Enter**” key. The result is “**TRUE**”, as shown below.

**3:**Drag the formula from cell**C2**to**C9**using the excel fill handle. The output is shown below.

**[Note:** Further, we can use conditional formatting too. The cells that are not matched are highlighted in yellow color, and cells **C3 **and** C6** are not matched, as shown in the below image.**]**

#### Example #2 – Case Sensitive Match

The following example depicts two sets of values, and we will **Compare and Match** Two Columns to the **Case Sensitive Match** using the **EXACT formula**.

In the table, the data is,

- Column A contains Value 1.
- Here, column B contains Value 2.
- Column C contains the Match Output.

The steps to **Compare and Match **Two Columns using the **Exact() function** are as follows:

**1:**Select cell**C2**, and enter the formula*=EXACT(A2,*

**2:**Select the cell that contains text 2, i.e., “**B2**”, and close the brackets. Now, the complete formula is*=EXACT(A2, B2)*

**3:**Press the “**Enter**” key. The result is “**TRUE**”, as shown below.

**4:**Drag the formula from cell**C2**to**C9**using the fill handle. The output is shown below.

**[Note:** Further, we can use conditional formatting too. The cells that are not matched are highlighted in green color, and cells **C3, C7, **and** C9** are not matched, as shown in the below image.**]**

#### Example #3 – Change Default Result TRUE or FALSE with IF Condition

The following example depicts two sets of string values, and we will Change the Default Result to TRUE or FALSE using the **IF excel formula**.

In the table, the data is,

- Column A contains String 1.
- Here, column B contains String 2.
- Column C contains the Match Output.

The steps to **Compare and Match** Two Columns using the **IF() formula** are as follows:

**1:**Select cell**C2**, and enter the formula*=IF(A2=B2,*

[**Note:** Select the cell that contains logical test condition, i.e., **“A2=B2.”]**

**2:**Select the cell that contains a value if true, i.e., “**Matched String**”.

**3:**Select the cell that contains a value if false, i.e., “**Unmatched String**”, and close the brackets. The complete formula is*=IF(A2=B2, “Matched String,” “Unmatched String”).*

**4:**Press the “**Enter**” key. The result is “**Unmatched String**”, as shown below.

**5:**Drag the formula from cell**C2**to**C5**using the fill handle. The output is shown below.

**[Note:** The cells that are not matched are highlighted with yellow color using conditional formatting. Cells **C2 and C4** are not matched, as shown in the below image.**]**

#### Example #4 – Highlight Matching Data

The following example depicts two sets of values, and we will **Compare and Match** Two Columns and **Highlight Matching Data**.

In the table, the data is,

- Column A contains Value 1.
- Column B contains Value 2.

The steps to **Compare and Match** Two Columns and highlight the result are as follows:

**1:**First, select the cells containing the values, i.e.,**A2:B6**. Then, select the “**Home**” tab → go to the “**Styles**” group → click the “**Conditional Formatting**” option drop-down → select the “**New Rule…**” option, as shown below.

**2:**The “**New Formatting Rule**” window pops up.- Select the “
**Use a formula to determine which cells to format**” option from the “**Select the Rule Type:**” list. - Enter the formula
**“=$A2=$B2”**in the “**Edit the Rule Description:**”. - Select the “
**Format**” button.

- Select the “

**3:**The “**Format Cells**” window pops up.- Select the color from the
**Fill**option from the menu, here, Blue color. - Click
**OK**in the “**Format Cells**” window.

- Select the color from the

**4:**Click**OK**in the “**New Formatting Rule**” window.

**5:**The matched cells are highlighted as shown in the below image.

### Important Things To Note

**Compare and Match**columns**in**Excel to check each cell against the other cells to identify a match.- If the match is missing, then the relevant message is returned.
**Compare and Match**columns in Excel can be done by highlighting each column’s unique or duplicate values.- Conditional Formatting is used to display unique or duplicate cells or columns.

### Frequently Asked Questions (FAQs)

**1. Why is it Useful to Compare Two Columns in Excel?**

Excel uses data for storage, manipulation, and decision-making that helps convey information to the user. Data analysis tools gather the information used in marketing and sales decisions. The formula does not contain information. Some datasets are huge and linked to each other. A data analyst needs to compare two columns across the same or different spreadsheets, and doing it manually is a tedious task. It takes a huge amount of time to find the missing data.

Comparing two columns in Excel finds out whether the cell contains matched data. It displays as output TRUE or FALSE, for Match or Not Match, or any other message defined by the user.

**2. How do you use VLOOKUP function to Compare and Match the Excel column?**

The following example depicts two sets of values, and we will **Compare and Match Two Columns** in Excel to using **VLOOKUP Excel formula**.

In the table, the data is,**• **Column A contains Value 1.**• **Column D contains Value 2.**• **Column E contains the Match Output.

The steps to **Compare and Match** using **VLOOKUP()** are as follows:**• 1:** Select cell **E2**, and enter the formula *=VLOOKUP(A2, D2:D4, 1, TRUE)***[Note: The arguments are, **the cell that contains a lookup value, i.e., “**A2**”, the cell that contains a table array, i.e., “**D2:D4**”, the cell that contains a column index number, i.e., “**1**”, and the cell that contains a range lookup, i.e., “**TRUE**”.]**• 2:** Press the “**Enter**” key. The result is “**Apple**”, as shown below.**• 3:** Drag the formula from cell E2 to E4 using the fill handle. The output is shown below.

**3. What are the other methods to compare two columns in Excel using the IF condition?**

The other methods used to Compare two Columns in Excel are by using **AND **and** OR** functions along with the **IF function.****• **To find matches in all cells within the same row in the table of the same values, the IF-AND formula is** =IF(AND(A2=B2, A2=C2), “Full match,” “No match”).****• **To find matches if any two cells are in the same row, the** IF-OR** formula is** =IF(OR(A2=B2, B2=C2, A2=C2), “Match,” “No match”)**.

### Download Template

This article must help understand the **Compare and Match Columns in Excel** formula and examples. You can download the template here to use it instantly.

### Recommended Articles

This has been a guide to Compare And Match Two Columns In Excel. Here we learn this using basic and excel built-in methods along with examples & downloadable template. You can learn more from the following articles –

## Leave a Reply