Compare number to range; find highest positive difference

[RBW]

I need to compare the value in a cell to a range of values, and select the
value in the range that has the smallest positive difference. If the index
cell is 4, and the range has 1,2,3 and 9, I need the range to pick 9. I've
tried to use =min(index no. - range), but that doesn't seem to work; vlookup
doesn't seem to work, either.

Grateful for your thoughts.

 

[T. Valko]

Try this array formula** :

A1 = base number
C1:C4 = range of numbers

=INDEX(C1:C4,MATCH(TRUE,C1:C4-A1=MIN(IF(C1:C4-A1>=0,C1:C4-A1)),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[RBW]

Biff-

Many thanks- that works perfectly. Impressive coding!

RBW

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Dana DeLouis]

Not sure, but this array formula might be another option.
It assumes your input data is in the range 1-9.

=MIN(IF(C1:C4>A1,C1:C4))

Note that if the input is 3, it rounds up to 9.
This is because you said "the smallest positive difference."
A returned value of 3 is a zero difference is not what you asked.
Perhaps you meant non-negative (0 or better).
- - -
HTH :>)
Dana DeLouis

 

[T. Valko]

Wow!

You think I made that more complicated than need be? <g>

 

[RBW]

Dana-

Appreciate the thought. The actual application is to compare the date an
expense was incurred (the index cell) against the range of dates of invoices
sent to a client (hence the need for only a positive difference- you can't go
back and add to a past invoice ). I suppose an expense could be
incurred and billed on the same day (the "0" outcome), but would guess it's
unlikely, so a positive number is probably what the formula needs to find.

A nice enhancement, which I think I can do using the =IF function, would be
to show, for expense dates newer than any invoice, a message along the lines
of, "Not yet billed" or something like that.

FWIW, I'm looked at as understanding Excel pretty well, but every time I
come here, I realize how little I know compared to all of you. Really
appreciate everyone's time in thinking through issues like this.