# min, match, offset problem

#### seanh1016

##### Active Member
I have two columns, column A=months, column B=values. Column B has some negative values in it. How do I find the closest value to zero in column B, then figure out which month this value occurs?

I don't want the lowest number -- just the one closest to zero. Would the min function still work here?

Thanks

### 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"

##### MrExcel MVP
What happens if you have 0 in B-range? The closest value should be 0, right?

#### seanh1016

##### Active Member
I'm dealing with payments here. Very rarely will there be a zero in column B. But I guess we'd have to factor that in.

##### MrExcel MVP
seanh1016 said:
...But I guess we'd have to factor that in.

Meaning?

#### seanh1016

##### Active Member

meaning maybe if the min function returns a negative number since it's looking the lowest number, we may have to use the absolute value and min together in a formula.

I tried this formula, but I came up with #VALUE!:

=INDEX(\$B\$20,MATCH(MIN(\$B\$20:\$B\$379),\$B\$20:\$B\$379,FALSE),-1)

what am I doing wrong here?

By the way, I would use colo's utility, but I have some sensitive material in this file. Sorry.

##### MrExcel MVP
seanh1016 said:
meaning maybe if the min function returns a negative number since it's looking the lowest number, we may have to use the absolute value and min together in a formula.

What I'm asking you is the following: B-range houses one or more zeroes along with neg and pos numbers. The value closest to 0 will invariably be 0. I'd think that 0 as the closest to 0 should be accepted as is.

...
By the way, I would use colo's utility, but I have some sensitive material in this file...

Create a copy of the relevant range. Anonimize the figures and names in the copy. Select a small portion the anonimized copy and post.

#### seanh1016

##### Active Member

We're only looking at the 1st and 2nd columns.

I tried =OFFSET(B20,MATCH(MIN(\$B\$20:\$F\$379),\$B\$20:\$B\$379,-1)-1,-1,1,1) and it came up with the 360th month - which is the wrong month in this case. The correct month here would be the 13th month.

Would the SMALL function or ABS have any purpose here?

##### MrExcel MVP
seanh1016 said:
...The correct month here would be the 13th month...

Thus, not the 14th month corresponding to: -58.26 ?

#### seanh1016

##### Active Member
Yes, not the 14th month b/c it's showing that the loan has been paid off in the 13th month. The payoff month is all I want to show.

##### MrExcel MVP
seanh1016 said:
Yes, not the 14th month b/c it's showing that the loan has been paid off in the 13th month. The payoff month is all I want to show.

Now we are getting closer to the right specs: You want in fact the smallest positive value, closest to 0, right?

Replies
3
Views
202
Replies
11
Views
316
Replies
10
Views
197
Replies
14
Views
277
Replies
4
Views
176

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

1,151,560
Messages
5,765,095
Members
425,258
Latest member
brentmitchell

### 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.

### Which adblocker are you using?

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

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