Maybe a little easier to understand:

=MAX((A1:D1)*(A1:D1<=E1))

This is also an array formula.


Mel wrote:
OMG! Thank you. You are amazing. Now I'll attempt to understand the
functions used so I know how to use for the future!!! Perfect. Again,
thankyou.

"Mike H" wrote:

Hi,

I'm guessing the range A1 - D1 isn't sorted so try this

=INDEX(A1:D1,MATCH(MIN(ABS(A1:D1-E1)),ABS(A1:E1-E1),0))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

"Mel" wrote:

I thought the HLOOKUP would be sufficient. Not the case.

I have 4 dollar amounts in Row 1 (A1:D1) and a dollar amount in E1. E1 is
the main value that must be compared to A1:D1 and the result is to return a
value found in A1:D1 closest BUT not exceeding E1.

Any suggestions or thoughts would be GREAT! Thank you.