Excel matching multiple cells for duplicates

Question

I need to populate a cell where the result is either valid or an error based on the following criteria. I'm not sure if using Match, Lookup formulas will work for this problem.

Given

       A           B            C
  +-----------+-----------+----------
1 | IntRef    | Value     | Result
2 |-----------|-----------+----------
3 | r01       | Value 123 | Success  (because B4 matches B3)
4 | r01       | Value 123 | Success  (because B3 matches B4)
5 | r02       | Value ABC | Failed   (because B6 differs from B5)
6 | r02       | Value XYZ | Failed   (because B5 differs from B6)

Success Criteria

Scan each IntRef (A) column for all duplicate keys. Where they match on a row check the Value column (B). Where all matching cells have the same value set their result cell (C) to Success.

Failed Criteria

Scan each IntRef (A) column for all duplicate keys. Where they match on a row check the Value column (B). Where all matching cells have a different value set their result cell (C) to Failed.

I am sure there is a formula that can be entered into each cell of column C which will do a lookup for each IntRef cross referencing the contents of column B where the match occurs. This is going beyond Excel formula knowledge.

Is it possible to create and help formulate the calculation of the success/failed criteria (Column C)?

MrBlue MrBlue · Accepted Answer · 2014-04-14T05:43:03

This appears to do the trick...

{=IF(COUNT(IF($B$3:$B$6=B3,IF($C$3:$C$6=C3,1)))=COUNTIF($B$3:$B$6,B3),"Success","Failed")}

Note that that's an array lookup formula (meaning you need to hit Ctrl+Shift+Enter when entering it).

This formula basically counts the number of times the A and B column values appear together and compares this to the number of times the A column value appears. If the two counts match, you have success.

Excel matching multiple cells for duplicates

2 Answers