On Mon, 22 Aug 2011 09:29:09 -0700 (PDT), cardan wrote:

On Aug 20, 8:37*am, Don Guillett wrote:
On Aug 19, 7:46*pm, Pete_UK wrote:

Hi Don,

that's what the OP asked for.

Pete

On Aug 19, 10:10*pm, Don Guillett wrote:

If you ask for 12 with this formula you will get the higher figure- Hide quoted text -

- Show quoted text -

I MEANT (getting old) if the OP asked for 13 they would NOT get 13 but
would get 20

Hello All, Thank you for the quick responses. I apologize if I
started a heated debate on symantics and the use of the english
language. I didn't think too much about the exactness of my post. I
kinda of assumed I could add to a given solution with an equal
sign.

What I should clarify is this. For an example, if the input number is
5 and the range in Column A has the numbers, 1, 5, 13, etc... the
formula should return the number in the corresponding range in Column
B. (the number 6 in my original post.). If the input number is greater
than 5 or equal to 13, (keeping with my original example again), then
it should return the number in column B corresponding with the same
row as the 13. I hope I am explaining this correctly.
The Index Match, formula posted by Pete_UK works very well for finding
and returning the maximum, however if my input number is five, it
returns. the number corresponding to the 13. I would like it to
return the number corresponding to the 5. I realize this was due to
my ambuguity in my OP. I apologize again. Is there a way to get the
formula to say if the input number is equal to a number in column A,
return the number in column B in the same row, otherwise, if the input
number is in between two numbers, return the number in column B that
corresponds to the larger number in column A. I hope this is more
clear. Again, Thank you for your time.


The array formula I posted three days ago will do exactly that:

=INDEX($B$1:$B$20,MATCH(TRUE,$A$25<=$A$1:$A$20,0))

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.