Skip to main content

Check if value exists in another column in Excel – Full Guide

Author: Xiaoyang Last Modified: 2024-05-24

In Excel, verifying whether values from one column exist in another is a common task that can be approached through different methods, each suitable for either exact or partial matches. Highlighting these values can further assist in quickly identifying matches visually.

Suppose you have two columns of data, Column A and Column C, that both contain some duplicate values. Manually comparing these columns one by one is inefficient. This guide will explain how to check for value existence and highlight them in Excel.

Check if value exists in another column with formulas

Highlight value if it is found in another column


Check if value exists in another column with formulas

To determine if a value in one column exists in another, the following formulas can do you a favor:

● Exact match:

To check for an exact match between values in two columns:

1. Please apply any one of the following formulas you like to a cell next to your data:

=IF(COUNTIF($A$2:$A$12, C2)>0, "Yes", "No")
=IF(ISNUMBER(MATCH(C2, $A$2:$A$12, 0)), "Yes", "No")
=IF(ISNA(VLOOKUP(C2, $A$2:$A$12, 1, FALSE)), "No", "Yes")
=IF(ISNA(MATCH(C2, $A$2:$A$12, 0)), "No", "Yes")
Note: In the formula, C2 represents the cell whose value you want to check against another column. The range $A$2:$A$12 is the column you are comparing it to.

2. Then, drag the formula down to apply it to other cells. It checks each value in Column C against values in Column A. If there’s a match, it returns Yes, otherwise, No is displayed. See screenshot:

● Partial Match:

For partial matches, where you want to check if part of a text string in Column C exists within any string in Column A, please apply the following formulas:

1. Enter or copy any one of the following formulas into a cell to get the result:

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(C2, $A$2:$A$12)))), "Yes", "No")
=IF(COUNTIF($A$2:$A$12,"*"&C2&"*")>0, "Yes", "No")
Note: In the formula, C2 represents the cell whose value you want to check against another column. The range $A$2:$A$12 is the column you are comparing it to, to see if it contains the value from C2.


Highlight value if it is found in another column

Highlighting values in an Excel spreadsheet that appear in another column can significantly aid in data analysis, making it easier to spot duplicates or significant relationships between datasets. This section will explain how to highlight values found in another column using both Kutools' AI Aide and Excel's built-in Conditional Formatting, covering both exact and partial matches.

Highlight value if it is found in another column with Kutools AI Aide

Kutools for Excel’s AI Aide feature can quickly determine whether a specific value exists in a designated column of your Excel sheet, handling both exact and partial matches with ease. Simply input your query, and Kutools AI Aide will analyze and execute the necessary actions. This powerful tool takes the hassle out of manually searching through columns and enables you to highlight matching values quickly, making it an indispensable aid for enhancing productivity and accuracy in your data management tasks.

After downloading and installing Kutools for Excel, click Kutools AI > AI Aide to open the Kutools AI Aide pane.

1. In the pane, type the following requirement into the chat box, and click Send button or press Enter key to send the question.

  • Exact match:
  • "Please check if value in Column C exists in Column A, highlight them with light blue color."
  • Partial Match:
  • "Please check if value in Column C exists in Column A (partial match), highlight them with light blue color."

2. After analyzing, click Execute button to run. Kutools AI Aide will process your request using AI and return the results directly in Excel.

 

Highlight value if it is found in another column with Conditional Formatting

Excel's Conditional Formatting feature is a powerful tool for visualizing relationships between data points by highlighting exact and partial matches. This section will walk you through the steps to set up Conditional Formatting for both exact and partial matches.

Step 1: Select Your Data

Select the range in your sheet where you want to apply the highlighting. This will typically be the column where you are looking for matches. Here, I will select the data in Column C.

Step 2: Apply Conditional Formatting

1. Click Home > Conditional Formatting > New Rule, in the New Formatting Rule dialog box:

  • Click Use a formula to determine which cells to format from the Select a Rule Type section;
  • And then enter the following formula into the Format values where this formula is true text box;
  •  =COUNTIF($A$2:$A$12, C2) > 0
  • Click Format button and choose the formatting options (e.g., a specific fill color) you want to apply to cells that meet the criteria.

2. Then, click OK > OK to close the dialogs.

Result:

Now, you can see if a value in Column C exists in Column A, it will be highlighted.

Tips:

To highlight values that partially match, adjust the formula as demonstrated below, and apply it into the Conditional Formatting.

 =SUMPRODUCT(--(ISNUMBER(SEARCH(C2, $A$2:$A$12))))>0

This will highlight if any part of the string in C2 is found within the range A2 to A12.

Whether you're aiming to verify exact or partial matches between columns in Excel, or highlight those matches, the methods outlined here—formulas, Kutools' AI Aide, and Conditional Formatting—offer robust solutions. You can choose the method that best suits your needs. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials, please click here to access them. Thank you for reading, and we look forward to providing you with more helpful information in the future!

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (9)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
If this formula dosn't work for you            =IF(ISERROR(VLOOKUP(A4,$C$4:$C$14, 1, FALSE)),"Not Exist","Exist" )
Try using this one :

=IF(ISERROR(VLOOKUP(A4;$C$4:$C$1840; 1; FALSE));"Not Exist";"Exist" )





This comment was minimized by the moderator on the site
This was really helpful for me. Thanks!
This comment was minimized by the moderator on the site
what if you want to know "where" the data is in the other list, not just that the data exists in the other list?
This comment was minimized by the moderator on the site
Bloody Brilliant work, thanks a lot.
This comment was minimized by the moderator on the site
Thank you for saving my time
This comment was minimized by the moderator on the site
This Guide is really AWESOME - a little too much GIF sparkles (using old firefox)
This comment was minimized by the moderator on the site
Thanks. It helped me in a great way.
This comment was minimized by the moderator on the site
That's new! Awesome!
This comment was minimized by the moderator on the site
Nice! This saved me some time. Can you compare col a and col c and write matches to col d?
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations