Comparing Numbers + closest match

Assuming that A1:B10 contain the data, try...

D1, confirmed with CONTROL+SHIFT+ENTER, and copied to E1:

=INDEX(A$1:A$10,MATCH(MIN(ABS($A$1:$A$10-$B$1:$B$10)),ABS($A$1:$A$10-$B$1:$B$10),0))

Thanks alot for that mate, it worked.

The thing is that it gives me just one of the numbers. As in the upper examples, I would like that formula to show me 90 and 92

or maybe 90 in one cell and then a formula that will look what is the number next to 90 (which will be 92)

Have you got any ideas on the highest positive + lowest positive (200,0)

thanks again for the help
much appreaciated

janko said:

Thanks alot for that mate, it worked.

Click to expand...

You're welcome!

The thing is that it gives me just one of the numbers. As in the upper examples, I would like that formula to show me 90 and 92

Click to expand...

As I mentioned in my previous post, after you've entered the formula in D1, copy the formula to E1. Then you'll have your second number.

Have you got any ideas on the highest positive + lowest positive (200,0)

Click to expand...

How are you arriving at those results?

yeah it worked, I didn't pay attention to the first post, thanks

For the second part, as the numbers keep ascending or descending, there will be a number (the highest positive before the lowest negative).
lets say
------------
5
2
0
-1
-4
------------
here I would like the number next to 0 in the next column to be placed in a cell along with the 0.

In my first column I have 200 and next to it is that 0 from the example above.

I don't know whether I have explained right, .... sorry if it is a confusing

again many thanks for the help

Still somewhat unclear. Does this return the desired results in all cases?

F1, copied to G1:

=INDEX(A$1:A$10,MATCH(0,$B$1:$B$10,-1))

D8 E8
<table border="0" cellpadding="0" cellspacing="0" width="164"><colgroup><col style="mso-width-source:userset;mso-width-alt:2998; width:62pt" span="2" width="82"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:62pt" height="20" width="82">-78.75</td> <td class="xl65" style="width:62pt" width="82">175.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">-73.85</td> <td class="xl65">173.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">-68.95</td> <td class="xl65">171.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">-64.05</td> <td class="xl65">169.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">-59.15</td> <td class="xl65">167.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">-54.25</td> <td class="xl65">165.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">-49.35</td> <td class="xl65">163.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">-44.45</td> <td class="xl65">161.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">-39.55</td> <td class="xl65">159.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">-34.65</td> <td class="xl65">157.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">-29.75</td> <td class="xl65">155.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">-24.85</td> <td class="xl65">153.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">-19.95</td> <td class="xl65">151.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">-15.05</td> <td class="xl65">149.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">-10.15</td> <td class="xl65">147.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">-5.25</td> <td class="xl65">145.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">-0.35</td> <td class="xl65">143.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">4.55</td> <td class="xl65">141.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">9.45</td> <td class="xl65">139.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">14.35</td> <td class="xl65">137.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">19.25</td> <td class="xl65">135.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">24.15</td> <td class="xl65">133.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">29.05</td> <td class="xl65">131.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">33.95</td> <td class="xl65">129.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">38.85</td> <td class="xl65">127.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">43.75</td> <td class="xl65">125.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">48.65</td> <td class="xl65">123.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">53.55</td> <td class="xl65">121.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">58.45</td> <td class="xl65">119.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">63.35</td> <td class="xl65">117.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">68.25</td> <td class="xl65">115.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">73.15</td> <td class="xl65">113.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">78.05</td> <td class="xl65">111.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">82.95</td> <td class="xl65">109.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">87.85</td> <td class="xl65">107.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">92.75</td> <td class="xl65">105.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">97.65</td> <td class="xl65">103.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;font-size:11.0pt;color:#9C0006; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;background:#FFC7CE;mso-pattern:black none" height="20">102.55</td> <td class="xl65">101.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">107.45</td> <td class="xl65">99.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">112.35</td> <td class="xl65">97.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">117.25</td> <td class="xl65">95.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">122.15</td> <td class="xl65">93.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">127.05</td> <td class="xl65">91.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">131.95</td> <td class="xl65">89.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">136.85</td> <td class="xl65">87.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">141.75</td> <td class="xl65">85.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">146.65</td> <td class="xl65">83.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">151.55</td> <td class="xl65">81.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">156.45</td> <td class="xl65">79.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">161.35</td> <td class="xl65">77.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">166.25</td> <td class="xl65">75.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">171.15</td> <td class="xl65">73.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">176.05</td> <td class="xl65">71.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">180.95</td> <td class="xl65">69.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">185.85</td> <td class="xl65">67.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">190.75</td> <td class="xl65">65.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">195.65</td> <td class="xl65">63.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">200.55</td> <td class="xl65">61.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">205.45</td> <td class="xl65">59.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">210.35</td> <td class="xl65">57.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">215.25</td> <td class="xl65">55.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">220.15</td> <td class="xl65">53.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">225.05</td> <td class="xl65">51.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">229.95</td> <td class="xl65">49.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">234.85</td> <td class="xl65">47.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">239.75</td> <td class="xl65">45.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">244.65</td> <td class="xl65">43.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">249.55</td> <td class="xl65">41.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">254.45</td> <td class="xl65">39.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">259.35</td> <td class="xl65">37.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">264.25</td> <td class="xl65">35.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">269.15</td> <td class="xl65">33.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">274.05</td> <td class="xl65">31.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">278.95</td> <td class="xl65">29.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">283.85</td> <td class="xl65">27.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">288.75</td> <td class="xl65">25.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">293.65</td> <td class="xl65">23.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">298.55</td> <td class="xl65">21.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">303.45</td> <td class="xl65">19.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">308.35</td> <td class="xl65">17.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">313.25</td> <td class="xl65">15.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">318.15</td> <td class="xl65">13.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">323.05</td> <td class="xl65">11.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">327.95</td> <td class="xl65">9.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">332.85</td> <td class="xl65">7.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">337.75</td> <td class="xl65">5.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">342.65</td> <td class="xl65">3.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">347.55</td> <td class="xl65">1.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">352.45</td> <td class="xl65">-1.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">357.35</td> <td class="xl65">-3.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">362.25</td> <td class="xl65">-5.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">367.15</td> <td class="xl65">-7.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">372.05</td> <td class="xl65">-9.00</td> </tr> </tbody></table>d100 e100
------------------------
that is an example table, that table changes, so it can be different numbers:
the highest positive with the lowest positive is 347.55 and 1.00

this is what i'd like to get in a table mate

thanks

Then it looks like the formula I offered should return the desired result. Did you try it?

hi

i didn't place the formula correctly the first time, it works now

thanks very much again for the help


all the best

You're very welcome! Glad I could help!