I am looking for a way to utilize the max or maxa functions that returns a
value depending on if it's less than a value. Assume:

B1 value is 8
A1:A5 values a
2
4
6
9
15

I want a function that returns one of the values in A1:A5 that is the
largest number less than B1. This example would return the value 6. I
basically need a max function something like this:

Max(A1:A5)<B1 (Return the maximum number of A1:A5 that's less than B1)

This returns a false statement because the maximum number of A1:A5 is not
less than B1, but I want the value of 6 to be returned because it is the
largest number in the array that's less than B1. PLEASE HELP.

Thanks in advance for anything from anyone!

=VLOOKUP(B1-0.000000000001,A1:A5,1)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"masterbaker" wrote in message ...
|I am looking for a way to utilize the max or maxa functions that returns a
| value depending on if it's less than a value. Assume:
|
| B1 value is 8
| A1:A5 values a
| 2
| 4
| 6
| 9
| 15
|
| I want a function that returns one of the values in A1:A5 that is the
| largest number less than B1. This example would return the value 6. I
| basically need a max function something like this:
|
| Max(A1:A5)<B1 (Return the maximum number of A1:A5 that's less than B1)
|
| This returns a false statement because the maximum number of A1:A5 is not
| less than B1, but I want the value of 6 to be returned because it is the
| largest number in the array that's less than B1. PLEASE HELP.
|
| Thanks in advance for anything from anyone!

Thanks Niek, that works.

Would you mind telling me how the hell that works??? Essentially it's
looking up a value that doesn't exist, so what is it actually looking up??

THanks a ton!!


"Niek Otten" wrote:

=VLOOKUP(B1-0.000000000001,A1:A5,1)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"masterbaker" wrote in message ...
|I am looking for a way to utilize the max or maxa functions that returns a
| value depending on if it's less than a value. Assume:
|
| B1 value is 8
| A1:A5 values a
| 2
| 4
| 6
| 9
| 15
|
| I want a function that returns one of the values in A1:A5 that is the
| largest number less than B1. This example would return the value 6. I
| basically need a max function something like this:
|
| Max(A1:A5)<B1 (Return the maximum number of A1:A5 that's less than B1)
|
| This returns a false statement because the maximum number of A1:A5 is not
| less than B1, but I want the value of 6 to be returned because it is the
| largest number in the array that's less than B1. PLEASE HELP.
|
| Thanks in advance for anything from anyone!

Niek's solution is making use of the Range_Lookup argument of the VLOOKUP
function. It's an optional final argument which dictates whether Vlookup
should return only an exact match, or the largest number which is smaller
than the number being sought. If the Range_Lookup argument is FALSE, an exact
match is required. If TRUE or omitted, the closest match is returned.

Regards,

Hutch

"masterbaker" wrote:

Thanks Niek, that works.

Would you mind telling me how the hell that works??? Essentially it's
looking up a value that doesn't exist, so what is it actually looking up??

THanks a ton!!


"Niek Otten" wrote:

=VLOOKUP(B1-0.000000000001,A1:A5,1)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"masterbaker" wrote in message ...
|I am looking for a way to utilize the max or maxa functions that returns a
| value depending on if it's less than a value. Assume:
|
| B1 value is 8
| A1:A5 values a
| 2
| 4
| 6
| 9
| 15
|
| I want a function that returns one of the values in A1:A5 that is the
| largest number less than B1. This example would return the value 6. I
| basically need a max function something like this:
|
| Max(A1:A5)<B1 (Return the maximum number of A1:A5 that's less than B1)
|
| This returns a false statement because the maximum number of A1:A5 is not
| less than B1, but I want the value of 6 to be returned because it is the
| largest number in the array that's less than B1. PLEASE HELP.
|
| Thanks in advance for anything from anyone!

basically need a max function something like this:
Max(A1:A5)<B1

Here's an Array equation. (Ctlr+Shft+Enter)

=MAX(IF(A1:A5<B1,A1:A5))

--
HTH. :)
Dana DeLouis
Windows XP, Office 2003


"masterbaker" wrote in message
...
I am looking for a way to utilize the max or maxa functions that returns a
value depending on if it's less than a value. Assume:

B1 value is 8
A1:A5 values a
2
4
6
9
15

I want a function that returns one of the values in A1:A5 that is the
largest number less than B1. This example would return the value 6. I
basically need a max function something like this:

Max(A1:A5)<B1 (Return the maximum number of A1:A5 that's less than B1)

This returns a false statement because the maximum number of A1:A5 is not
less than B1, but I want the value of 6 to be returned because it is the
largest number in the array that's less than B1. PLEASE HELP.

Thanks in advance for anything from anyone!