How to Find Matching Values in Two Columns in Excel

PDF download Download Article
Compare data with just a few simple commands
PDF download Download Article

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 wikiHow article will teach you how to find matching values in two columns in Excel.

Finding Matching Values in 2 Excel Columns

  1. Select the columns you want to compare.
  2. Click "Conditional Formatting" in the "Home" tab.
  3. Select "Highlight Cells Rule" and "Duplicate Values."
  4. Browse your columns for highlighted values.
Method 1
Method 1 of 3:

Using Conditional Formatting

PDF download Download Article
  1. 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.
  2. This will open up a drop-down menu with various additional options. [2]
    Advertisement
  3. This setting tells Excel that you want your conditional formatting to detect values that are duplicated (i.e., match) across your selected columns. [3]
  4. 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.
  5. 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.
    6. Advertisement
Method 2
Method 2 of 3:

Using VLOOKUP

PDF download Download Article
  1. 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.
  2. 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.
  3. 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]
  4. 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".
    5. Advertisement
Method 3
Method 3 of 3:

Using a TRUE/FALSE formula

PDF download Download Article
  1. 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.
  2. Assuming your data begins from the top-left corner of your spreadsheet, the formula is as follows: =A1=B1 .
  3. 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.
  4. Matching values will turn up a "TRUE" value. If there is no match, the box in the third column will read "FALSE." [7]
    5. Advertisement

Expert Q&A

Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Video

      Tips

      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Name
      Please provide your name and last initial
      Submit
      Thanks for submitting a tip for review!

      You Might Also Like

      How to
      Merge Cells in Microsoft Excel: A Quick Guide
      How to
      Unprotect an Excel Sheet
      How to
      Unhide All Rows and Columns in Microsoft Excel
      How to
      Use If‐Else in Microsoft Excel: Step-by-Step Tutorial
      How to
      Insert Pictures in Excel That Automatically Size to Fit Cells
      How to
      Create a Mortgage Calculator With Microsoft Excel
      How to
      Create a Chart in Microsoft Excel: Step-by-Step Guide
      How to
      Show Hidden Columns in Microsoft Excel & Unhide Column A
      How to
      Fix Ethernet Doesn't Have a Valid IP Configuration on Windows
      3 Easy Ways to Freeze Panes to Lock Columns or Rows in Excel
      How to
      Link Sheets in Excel
      How to
      Change from Lowercase to Uppercase in Microsoft Excel
      How to
      Make a Spreadsheet in Excel
      New to Excel? Here's Super Easy Tricks to Get You Started
      Advertisement

      References

      1. ↑ https://support.microsoft.com/en-us/office/highlight-patterns-and-trends-with-conditional-formatting-eea152f5-2a7d-4c1a-a2da-c5f893adb621
      2. ↑ https://support.microsoft.com/en-us/office/highlight-patterns-and-trends-with-conditional-formatting-eea152f5-2a7d-4c1a-a2da-c5f893adb621
      3. ↑ https://support.microsoft.com/en-us/office/highlight-patterns-and-trends-with-conditional-formatting-eea152f5-2a7d-4c1a-a2da-c5f893adb621
      4. ↑ https://www.geeksforgeeks.org/how-to-find-duplicate-values-in-excel-using-vlookup/
      5. ↑ https://www.automateexcel.com/functions/vlookup-formula-excel/
      6. ↑ https://www.geeksforgeeks.org/how-to-find-duplicate-values-in-excel-using-vlookup/
      7. ↑ https://support.microsoft.com/en-gb/office/true-function-7652c6e3-8987-48d0-97cd-ef223246b3fb

      About This Article

      Article Summary X

      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?
      In other languages
      Thanks to all authors for creating a page that has been read 100,966 times.

      Is this article up to date?

      Advertisement