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.

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.

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.

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.

With your headers in A1:D1 and values in A2:D2:

E2=MAX((A2:D2)*(A2:D2<=E2))

E1=INDEX(A1:D1,1,MATCH(F2,A2:D2,0))

E2 is an array formula, E1 is not.

Mel wrote:
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.

Sorry, that should have been:

F2=MAX((A2:D2)*(A2:D2<=E2))

F1=INDEX(A1:D1,1,MATCH(F2,A2:D2,0))

F2 is an array formula, F1 is not.


With your "main value" in E2.


Glenn wrote:
With your headers in A1:D1 and values in A2:D2:

E2=MAX((A2:D2)*(A2:D2<=E2))

E1=INDEX(A1:D1,1,MATCH(F2,A2:D2,0))

E2 is an array formula, E1 is not.

Mel wrote:
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.

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.

Thank you very much! The formula below was better in that it resolved the $0
values in E1 or below resulting in displaying the nearest value to zero.
Thank you again.

So here's another (I'm hoping the last obstacle):
These are my values in A1:D1 shown below:
$0 $0 $8,250,000 -$100,000
E1= $0
F1= - because the result was null
G1 = the header name of A1 because it matches "0".

The value I'm comparing these 4 columns to also = $0. Resulting (using the
formula below) in a header name populating when it should be BLANK.

The second formula suggest works on all situations except this one up above.
Here is the second formula where it displays the column header associated to
the value, that matched the closes but did not exceed,in the adjacent column.

E1=INDEX(A1:D1,1,MATCH(F2,A2:D2,0))

Thank YOU so much for responding so quickly and precisely!!!!!


"Glenn" wrote:

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.

Here is my attempt but no success

=IF(AY2=0),"",MAX(AU2:AX2*(AU2:AX2<=AY2))

I'm thinking this will display nothing (instead of zero) so that when I
execute this formula
=INDEX($AU$1:$AX$1,1,(MATCH(AZ4,AU4:AX4,0)))

it returns nothing as opposed to the header that actually has a zero dollar
amount in that row.

Make sense?


"Mel" wrote:

Thank you very much! The formula below was better in that it resolved the $0
values in E1 or below resulting in displaying the nearest value to zero.
Thank you again.

So here's another (I'm hoping the last obstacle):
These are my values in A1:D1 shown below:
$0 $0 $8,250,000 -$100,000
E1= $0
F1= - because the result was null
G1 = the header name of A1 because it matches "0".

The value I'm comparing these 4 columns to also = $0. Resulting (using the
formula below) in a header name populating when it should be BLANK.

The second formula suggest works on all situations except this one up above.
Here is the second formula where it displays the column header associated to
the value, that matched the closes but did not exceed,in the adjacent column.

E1=INDEX(A1:D1,1,MATCH(F2,A2:D2,0))

Thank YOU so much for responding so quickly and precisely!!!!!


"Glenn" wrote:

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.