max function with a less than or equal to

[Guest]

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 are:
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 Otten]

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|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 are:
| 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!

 

[Guest]

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!!

 

[Dana DeLouis]

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))

 

[Guest]

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

 

[Roger Govier]

Hi

I just posted a solution to your question as posted in an earlier
thread.