Mike -
I have another request associated to the original request, if you don't
mind. I'm requested to return the column header name of the result from your
previously suggested formula.

So for example the result from your formula = $505,000. That value came
from 80% replacement column. There are 4 columns that the result could come
from.

Any ideas?

"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.