This question already has an answer here:

This is what I would like to do: Tab A Column A has a mass of numbers, Tab B Column B will have numbers inputted into it. I need to have the Tab B Column B highlight showing a match to a number in Tab A or have a 'Yes' or 'No' answer appear in Tab B column A indicating a match. Any ideas how to do this?

marked as duplicate by Ramhound, Excellll, fixer1234, DavidPostill, mdpc Nov 7 '15 at 1:20

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Is this for Microsoft Excel? If so, please add the tag for it. – Steven Nov 4 '15 at 16:38

I'm not sure whether you want to check for a duplicate on the corresponding row in Sheet A or whether any cell in Column A on Sheet A has the value in the corresponding row in Sheet B. I'm assuming your first sheet is named Sheet1, the second sheet is named Sheet2, and both data sets start on the first row of their sheet.

Testing for duplicates in the entire column

Place =IF(COUNTIF(Sheet1!A:A,B1)>0,"YES","NO") in A1 on Sheet2. It will say "YES" or "NO" depending on whether any cell in column A on Sheet1 has the value in B1 on Sheet2. Drag the cell down to adjust it for more rows.

How it works: COUNTIF returns the number of cells in a range that satisfy a condition. The range Sheet1!A:A includes all cells in column A of Sheet1. They are tested for equality with B1 (which will be adjusted when you drag the cell). The IF function returns a value depending on the value of its condition, which is whether the number of cells from COUNTIF is nonzero.

Highlighting duplicates from the entire column

Added in response to a comment.

To highlight based on the presence of duplicates, conditional formatting needs to be applied. Click on the B column header in the second sheet to select the entire column, then choose Manage Rules under the Conditional Formatting button. Add a new rule with the type "Use a formula to determine which cells to format." Press the Format button to select what formatting will be applied. (Highlights can be done with the Fill tab.) Place =(COUNTIF(Sheet1!A:A,B1)>0) in the formula box, then click OK.

Testing for duplicates in the corresponding row

Place =IF(Sheet1!A1=B1,"YES","NO") in A1 on Sheet2. Again, it says "YES" or "NO" and you can drag it down to more rows.

How it works: the = between Sheet1!A1 (which refers to A1 on Sheet1) and B1 tests whether those cells' values are equal. IF functions the same as above.

  • Tried either, not what i was looking for. Wanted to basically have Sheet 2 Column B1 highlight if I type in the same number that was in Sheet 1 Column A:A (entire column). – R Robertson Nov 4 '15 at 19:12
  • @RRobertson I updated my answer, "highlighting duplicates from the entire column" should do what you want. – Ben N Nov 5 '15 at 16:17

Not the answer you're looking for? Browse other questions tagged or ask your own question.