Identifying positive/negative matches

sriche01

Board Regular
Joined
Dec 24, 2013
Messages
137
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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!
 
Upvote 0

Forum statistics

Threads
1,226,362
Messages
6,190,522
Members
453,611
Latest member
JRM59

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top