I'm looking for a way to return a specific cell value from a range based on an initial desired value. It looks like this:
DesiredValue typed in cell: | 125|
line1 RangeA|RangeB line2 1 | 50 line3 51 | 100 line4 101 | 500 line5 501 | 1500
I'm trying to get two formulas:
the first one returns the immediate bigger value in rangeA (500 in this case), the second one returns the immediate lower value (101 in this case).
Currently I'm using a very ugly if formula, for the greater value as an example:
if[DesiredValue]<=[b2], [B2], if [DesiredValue]<=[b3], [b3], if...
A modified version of this formula is used to get the lower number, but it is really not the way to do it since i'm gonna have to use it in a couple hundread ranges.
Thanks in advance!