How to Find Same Values in Two Columns in Excel Using a Formula?
When working with large datasets in Excel, it’s common to need to compare two columns to find matching values. This can be particularly useful in scenarios like identifying duplicates, validating data, or performing cross-referencing between lists. Fortunately, Excel formulas provide efficient ways to achieve this.
In this article, we will cover how to use Excel formulas to find same values in two columns, ensuring accuracy and saving time. We will explore different approaches using built-in functions like IF, VLOOKUP, MATCH, and COUNTIF.
Why Finding Matching Values in Two Columns is Important
Before we dive into the actual formulas, let’s first understand why this task is critical:
- Data Validation: Ensuring that information from two datasets matches is key in verifying accuracy.
- Removing Duplicates: You may need to identify and eliminate duplicate entries.
- Data Comparison: When combining data from different sources, finding matches ensures you’re not missing key information.
Let’s now get into the practical ways to find matching values in Excel.
Comparing Two Columns Using the IF Formula
One of the most straightforward ways to find same values in two columns is by using the IF function. The IF formula evaluates whether a condition is true or false, and you can use it to compare two cells.
Syntax of the IF Function
=IF(condition, value_if_true, value_if_false)
In our case, the condition will be whether the value in Column A equals the value in Column B.
Example
Assume you have data in Column A (A2:A10) and Column B (B2:B10), and you want to find out if the values in these two columns match.
- Select a new column (e.g., Column C).
- Enter the following formula in C2:
=IF(A2=B2, "Match", "No Match")
- Drag the formula down to compare all values in the range.
Column A | Column B | Column C |
---|---|---|
Apple | Apple | Match |
Banana | Orange | No Match |
Cherry | Cherry | Match |
Date | Banana | No Match |
This simple formula helps you easily see if the values in two columns are the same.
Using the VLOOKUP Formula to Find Matches
The VLOOKUP function is one of Excel’s most popular tools for looking up values. It’s ideal for finding matching values between columns, especially when dealing with large datasets.
Syntax of the VLOOKUP Function
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
For this task, we will use VLOOKUP to search for a value from Column A in Column B.
Example
Let’s say you have data in Column A and Column B, and you want to find if values from Column A exist in Column B.
- In Column C, enter the following formula:
=IF(NOT(ISNA(VLOOKUP(A2, B:B, 1, FALSE))), "Match", "No Match")
Here’s what happens:
- VLOOKUP(A2, B:B, 1, FALSE) looks for the value in A2 within Column B.
- ISNA checks if VLOOKUP returns an error (if the value isn’t found).
- IF displays “Match” if the value is found and “No Match” if not.
Finding Same Values Using the COUNTIF Function
Another way to find matching values between two columns is by using the COUNTIF function. This function counts how many times a specific value appears in a given range.
Syntax of the COUNTIF Function
=COUNTIF(range, criteria)
In this case, we’ll check how many times a value from Column A appears in Column B.
Example
- Enter the following formula in C2:
=IF(COUNTIF(B:B, A2) > 0, "Match", "No Match")
- Drag the formula down.
Column A | Column B | Column C |
---|---|---|
Apple | Apple | Match |
Banana | Orange | No Match |
Cherry | Cherry | Match |
Date | Banana | No Match |
This approach uses COUNTIF to count the occurrences of each value in Column B, providing an efficient method for comparison.
Using the MATCH Function to Compare Two Columns
The MATCH function is also useful for comparing two columns in Excel. It returns the relative position of an item in a specified range.
Syntax of the MATCH Function
=MATCH(lookup_value, lookup_array, [match_type])
To find out if values in Column A exist in Column B, we will use the MATCH function combined with IF.
Example
- In C2, enter this formula:
=IF(ISNUMBER(MATCH(A2, B:B, 0)), "Match", "No Match")
- MATCH(A2, B:B, 0) looks for an exact match of the value in A2 within Column B.
- ISNUMBER checks if MATCH finds a value, returning a number if true and an error if false.
2. Drag the formula down for all cells in Column C.
This method is particularly useful when working with larger datasets, as MATCH can handle complex lookups effectively.
Highlight Matching Values in Two Columns Using Conditional Formatting
Here are the steps to find and highlight matching values in two columns using Conditional Formatting without bullet points:
- Select the First Column (e.g., Column A):
Highlight the range of cells in Column A where you want to find matching values. For example, select A2:A10. - Go to Conditional Formatting:
In the Excel ribbon, go to the Home tab and click on Conditional Formatting under the Styles group. - Create a New Rule:
From the dropdown, select New Rule. - Use a Formula to Determine Which Cells to Format:
In the New Formatting Rule dialog, choose Use a formula to determine which cells to format. - Enter the Formula to Find Matching Values:
In the formula input box, enter the following formula to compare values between Column A and Column B:
=MATCH(A2, B:B, 0)
This formula checks if each value in Column A appears anywhere in Column B. If the value is found, it returns a number indicating the row position in Column B where the match occurs.
- Set the Formatting Style:
Click the Format button to choose how you want to highlight the matching values. You can select a color fill, bold text, or any other style to easily identify the matches. After choosing your format, click OK. - Apply the Rule:
Click OK again to apply the rule and see the matching values highlighted in Column A. - Repeat for Column B (Optional):
If you want to highlight matches in Column B as well, repeat the process for Column B, but use this formula to compare values in Column B with Column A:
=MATCH(B2, A:A, 0)
Now, the matching values in both columns will be highlighted, allowing you to quickly identify duplicates or shared data across Column A and Column B.
Example
Column A | Column B |
---|---|
Apple | Orange |
Banana | Banana |
Cherry | Cherry |
Date | Grape |
After applying Conditional Formatting, the cells containing “Banana” and “Cherry” in Column A will be highlighted because they are also found in Column B.
Using Array Formulas to Find Matching Values
Array formulas allow you to perform complex calculations on multiple data sets simultaneously. One advanced method of comparing two columns for matching values is to use an array formula.
Example
- Enter the following formula in C2:
=IF(OR(A2=B2:B10), "Match", "No Match")
- Press Ctrl + Shift + Enter to activate the array formula.
This will compare each value in Column A with all the values in Column B and return “Match” for any found duplicates.
Combining INDEX and MATCH for Advanced Comparison
In more advanced scenarios, using a combination of INDEX and MATCH can give you more flexibility when comparing two columns. This approach allows you to find the position of matching values and return the corresponding result.
Syntax of INDEX and MATCH
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
In this formula:
- MATCH identifies the position of the matching value.
- INDEX returns the value from the specified range.
Example
If you want to return a corresponding value from another column based on a match between Column A and Column B, you can use the formula:
=IF(ISNUMBER(MATCH(A2, B:B, 0)), INDEX(B:B, MATCH(A2, B:B, 0)), "No Match")
This advanced technique can be useful when you need to retrieve associated data once a match is found.
Common Mistakes to Avoid When Finding Matching Values
While working with Excel formulas to find same values in two columns, there are a few common mistakes you should avoid:
- Forgetting to Lock Cell References: When using formulas like VLOOKUP or COUNTIF, it’s crucial to lock the reference range if you’re dragging the formula across multiple rows.
- Mismatched Data Types: Ensure both columns have the same data type (e.g., text, number) to avoid errors in matching.
- Using Inconsistent Ranges: Always double-check that your formulas are referring to the correct ranges to avoid missing matches.
Final Thoughts
In Excel, finding same values in two columns can be easily done with a variety of formulas such as IF, VLOOKUP, MATCH, and COUNTIF. Each of these formulas offers its own advantages depending on the complexity of the data and the comparison you need to perform. Additionally, you can use tools like Conditional Formatting for quick visual comparisons, or Array Formulas and INDEX-MATCH for more advanced scenarios.
With these methods, you can ensure accuracy and efficiency when comparing columns in Excel, making your workflow smoother and more productive.
FAQs
How do I find matching values in two columns in Excel?
To find matching values between two columns in Excel, you can use various formulas like IF, VLOOKUP, MATCH, or COUNTIF. These functions help compare values from one column to another and return ‘Match’ or ‘No Match’ based on the condition.
Can I use VLOOKUP to find matching values between two columns?
Yes, you can use the VLOOKUP function to find matching values between two columns. VLOOKUP searches for a value from one column in another column and returns ‘Match’ if the value is found.
Can I compare two columns using the COUNTIF function?
Yes, the COUNTIF function is a simple way to compare two columns in Excel. COUNTIF checks how many times a value from one column appears in another and returns ‘Match’ if the count is greater than zero.
How can I use the MATCH function to find duplicates in Excel?
You can use the MATCH function to compare values between two columns by checking if each value in one column appears in the other. Combine MATCH with IF or ISNUMBER to return ‘Match’ or ‘No Match’ for each comparison.
What is the advantage of using INDEX and MATCH over VLOOKUP?
INDEX and MATCH provide more flexibility than VLOOKUP because they can look for matches in any direction, not just from left to right. Additionally, INDEX and MATCH can handle more complex lookups and return more precise results.

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.