"Pete_UK" wrote:
I thought about pointing that out, but the OP did say
"... I have an input number that is somewhere between
the numbers in Column A... ", so I took this to mean
that the number in A25 would be less than the number
in A20.

Right. So when someone says "pick an integer between 1 and 10", you
honestly believe that only the numbers 2 through 9 are fair game? Yeah,
right.

News flash: English is an ambiguous language. That's why careful people
say "between this and that inclusively" or "between this and that
exclusively".

But most people are not that careful. In my experience, 98 times out of
100, when someone says "between x and y", they mean inclusively. YMMV.


Pete wrote:
and to be fully bullet-proof if the number might be less than that in A1:
=IF($A25<$A$1,"too small", [....]

The test for A25<A1 should not be necessary because "between" never includes
numbers less than the lower limit and more than the greater limit.

Of course, Carl might want to consider that if he has no control over data
in A25; for example, if he is providing this to others for their use.

But as you say: "although the OP could think about using data validation on
A25 to force acceptable numbers".

That still does not solve the ambiguity of the English word "between".


----- previous message -----

On Aug 19, 8:18 pm, "joeu2004" wrote:
"Pete_UK" wrote:
Try this in B25:
=INDEX(B$1:B$20,MATCH($A25,$A$1:$A$20)+1))

I have not read Carl's requirements in detail to know if this is or is not
a
problem, but just an observation for consideration....

That formula will result in a #REF error if MATCH returns 20 -- that is,
if
A25 is greater than or equal to A20.

----- previous message -----

On Aug 19, 7:04 pm, cardan wrote:



I have a question I was hoping someone could help me with.

I have column of numbers in ascending order, of about 20 or so rows.
The numbers ascend sporadically ie, say column A1:A20 the numbers
start … 1, 5, 13, 20, 25…..
In the next column over (B1:B20), I have other numbers, that are
random (but more or less ascending) 3.5, 6, 7.5, 11.86, 20.76….

In Cell A25, I have an input number that is somewhere between the
numbers in Column A. For example 12. I am trying to write a formula
that will search the range in column A and will recognize the input of
12 is in between the 5 and 13 and then return the value in Column B
that is on the same row of the 13. (ie the max value between the 5 and
13).
I know there is a formula out there and I am trying to avoid VBA and
also trying to avoid an array function-if possible

I hope I have explained this correctly. Please let me know if I need
to clarify. Thank you in advance.- Hide quoted text -

- Show quoted text -