Identifying positive/negative matches

[sriche01]

Hello all,

I have a problem that probably has a simpler answer than I realize, but I have been scratching my head all day over it.

So I have a row of numbers, Say R10:AD10, that has lots of zeros and occasional positive and negative numbers in it. Sometimes this row will have a positive number in one cell and a negative of the same integer in another cell, e.g. 6 and -6 as well as other positive or negative numbers that don't necessarily match each other in the other cells.

What i want is a cell to the right of this row that finds the two that match and identifies the absolute of it (6 in the example above). Again, these could be in any of the 13 cells in the row and in any order. And usually there will be lots of zeros and other numbers in the other cells in the row. I do not expect there to be two matching pairs, so for this exercise let's assume there won't be.

Thanks for your help in advance!

 

[pgc01]

Hi

Try:

=ABS(LOOKUP(2,1/(R10:AD10<>0)/MATCH(R10:AD10,-R10:AD10,0),R10:AD10))

 

[sriche01]

Hi

Try:

=ABS(LOOKUP(2,1/(R10:AD10<>0)/MATCH(R10:AD10,-R10:AD10,0),R10:AD10))

Thank you very much. I'm a little balder after trying to figure that out for several hours yesterday!