Download ArticleDownload ArticleX
This article was co-authored by Luigi Oppido and by How.com.vn staff writer, Cory Stillman. Luigi Oppido is the Owner and Operator of Pleasure Point Computers in Santa Cruz, California. Luigi has over 25 years of experience in general computer repair, data recovery, virus removal, and upgrades. He is also the host of the Computer Man Show! broadcasted on KSQD covering central California for over two years.
This article has been fact-checked, ensuring the accuracy of any cited facts and confirming the authority of its sources.
This article has been viewed 83,076 times.
Excel remains one of the most powerful tools in the Microsoft Office Suite, but it can be understandably daunting as well. Fortunately, we have broken down one of Excel's most essential features into just a few simple steps. This How.com.vn article will teach you how to find matching values in two columns in Excel.
Steps
Select the columns you would like to compare. Using conditional formatting in Excel will allow you to automatically highlight any matching values across multiple columns. Click and drag your mouse over the columns you would like to compare.[1]- If the two columns are not side by side, simply hold down Ctrl and select whichever columns you need.
Click Conditional Formatting from the "Home" tab. This will open up a drop-down menu with various additional options.[2]Advertisement
Select Highlight Cells Rule and then Duplicate Values. This setting tells Excel that you want your conditional formatting to detect values that are duplicated (i.e., match) across your selected columns. [3]
Click OK on the pop-up window. After selecting your conditional formatting settings, Excel will show you a pop-up window. Ensure the window reads Duplicate in the left-hand box, and click "OK."- The other box in the pop-up window allows you to change the colors Excel uses to indicate duplicates. The default is "Light Red Fill with Dark Red Text", but you may choose whichever you prefer.
Identify the matching values. Excel will now highlight any duplicates with the formatting you chose in the previous pop-up box. Look for this colored formatting and identify any matches.- Using conditional formatting to find matching values is a handy way to find matches that may not be in the same row.
Advertisement
Create a third column next to your two columns of data. The VLOOKUP function involves using a specific formula to find matching values. You'll need a third column to input the formula and display any matches.
Enter the VLOOKUP formula into the first row of the third column. Assuming your data begins from the top-left corner of your spreadsheet, the formula is as follows: =VLOOKUP(B1,$A$1:$A$17,1,FALSE).[4]- The "17" in the formula indicates 17 rows of data. Change the number to fit however many rows of data you have.
- The "FALSE" value at the end of the formula is what tells Excel to look for an exact match in value. Replace it with "TRUE" to search for the nearest match that is less than or equal to the corresponding data point (represented in this case by B1). [5]
- Just entering "=VLOOKUP" in Excel will pull up the full formula, which you can reference in populating each field with the necessary info.
Copy the VLOOKUP formula all the way down. Drag down from the corner of the first box to your final row of data to copy the formula. Excel will automatically change the first value to the corresponding data point in that row.[6]
Look for matching values in your third column. If there are any matching values, they will display as a number in your spreadsheet's third column. If there are no matching values, the VLOOKUP formula will simply turn up "#N/A".
Advertisement
Create a third column next to your two columns of data. This method involves using a specific formula to find matching values. You'll need a third column to input the formula and display its results.
Enter the TRUE/FALSE formula into the third column. Assuming your data begins from the top-left corner of your spreadsheet, the formula is as follows: =A1=B1.
Copy the formula all the way down. Drag down from the corner of the first box to your final row of data to copy the formula. Excel will automatically change the values to the corresponding data points in that row.
Look for a "TRUE" or "FALSE" assessment in the third column. Matching values will turn up a "TRUE" value. If there is no match, the box in the third column will read "FALSE."[7]
Advertisement
Expert Q&A
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
SubmitAdvertisement
Video
Tips
Submit a Tip
All tip submissions are carefully reviewed before being published
Thanks for submitting a tip for review!
References
- ↑ https://support.microsoft.com/en-us/office/highlight-patterns-and-trends-with-conditional-formatting-eea152f5-2a7d-4c1a-a2da-c5f893adb621
- ↑ https://support.microsoft.com/en-us/office/highlight-patterns-and-trends-with-conditional-formatting-eea152f5-2a7d-4c1a-a2da-c5f893adb621
- ↑ https://support.microsoft.com/en-us/office/highlight-patterns-and-trends-with-conditional-formatting-eea152f5-2a7d-4c1a-a2da-c5f893adb621
- ↑ https://www.geeksforgeeks.org/how-to-find-duplicate-values-in-excel-using-vlookup/
- ↑ https://www.automateexcel.com/functions/vlookup-formula-excel/
- ↑ https://www.geeksforgeeks.org/how-to-find-duplicate-values-in-excel-using-vlookup/
- ↑ https://support.microsoft.com/en-gb/office/true-function-7652c6e3-8987-48d0-97cd-ef223246b3fb
About This Article
Computer & Tech Specialist
This article was co-authored by Luigi Oppido and by How.com.vn staff writer, Cory Stillman. Luigi Oppido is the Owner and Operator of Pleasure Point Computers in Santa Cruz, California. Luigi has over 25 years of experience in general computer repair, data recovery, virus removal, and upgrades. He is also the host of the Computer Man Show! broadcasted on KSQD covering central California for over two years. This article has been viewed 83,076 times.
How helpful is this?
Co-authors: 2
Updated: February 26, 2024
Views: 83,076
Categories: Microsoft Excel
Article SummaryX
1.Use conditional formatting to highlight matching values.
2.Use VLOOKUP or a TRUE/FALSE formula to display matching values in a new column.
Did this summary help you?
Thanks to all authors for creating a page that has been read 83,076 times.
Is this article up to date?
⚠️ Disclaimer:
Content from Wiki How English language website. Text is available under the Creative Commons Attribution-Share Alike License; additional terms may apply.
Wiki How does not encourage the violation of any laws, and cannot be responsible for any violations of such laws, should you link to this domain, or use, reproduce, or republish the information contained herein.
- - A few of these subjects are frequently censored by educational, governmental, corporate, parental and other filtering schemes.
- - Some articles may contain names, images, artworks or descriptions of events that some cultures restrict access to
- - Please note: Wiki How does not give you opinion about the law, or advice about medical. If you need specific advice (for example, medical, legal, financial or risk management), please seek a professional who is licensed or knowledgeable in that area.
- - Readers should not judge the importance of topics based on their coverage on Wiki How, nor think a topic is important just because it is the subject of a Wiki article.
Advertisement
