Return a particular value in a range of cells?

[alanad]

Is there a way for Excel to look at 6 cells in a row and return the value that occurs twice?

For example in cells A B C D E F you have numbers 10,11,11,12,11,12. I want it to return the number 12, even if the data is inputed in a different order (10,11,12,11,11,12 or 11,10,11,12,12,11 etc.)


In my situation, the number 12 will never be in cell A or B, so maybe there is a way to search cells C, D, E, F, and then return the number that occurs in cells C, D, E or F, but does not occur in cells A or B?


Any suggestions would be greatly appreciated!

[Domenic]

In your example, if we look at C, D, E and F, 11 appears twice and 12 also appears twice. What would you like to have happen in this scenario? What if 11 appeared once and 12 appeared three times?

[freekrill]

Try in cell H1

=MAX(IF(COUNTIF($A$1:$G$1,A1:G1)=2,A1:G1,0))

entered as an array formula: ctrl+shift+enter

This will enter the largest value that appears exactly twice

Hope this helps
free

[alanad]

Works nice, thanks!