Match function
 

Hi
Please tell me workaround given my numbers are in ascending order and a need
a minimum.
I have to find the minimum value greater than a number in a row of numbers
placed in ascending order.
The MATCH function requires the numbers to be in descending order with last
parameter of Match set to -1.
Match_type Behavior
1 or omitted MATCH finds the largest value that is less than or equal to
lookup_value. The values in the lookup_array argument must be placed in
ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
0 MATCH finds the first value that is exactly equal to lookup_value. The
values in the lookup_array argument can be in any order.
-1 MATCH finds the smallest value that is greater than or equal to
lookup_value. The values in the lookup_array argument must be placed in
descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and
so on.

Match function
 

Try,

with your lookup value in A2

=MIN(IF(A1:H1=A2,A1:H1))

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

"rupert" wrote:

Hi
Please tell me workaround given my numbers are in ascending order and a need
a minimum.
I have to find the minimum value greater than a number in a row of numbers
placed in ascending order.
The MATCH function requires the numbers to be in descending order with last
parameter of Match set to -1.
Match_type Behavior
1 or omitted MATCH finds the largest value that is less than or equal to
lookup_value. The values in the lookup_array argument must be placed in
ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
0 MATCH finds the first value that is exactly equal to lookup_value. The
values in the lookup_array argument can be in any order.
-1 MATCH finds the smallest value that is greater than or equal to
lookup_value. The values in the lookup_array argument must be placed in
descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and
so on.

Match function
 

Figured it out. Create the array fn based on one row.
The just copy the array function ordinarily. Simple!

"rupert" wrote:

hi,

Thanks. This works as an array function:

=MIN(IF(IF(J697:R697=$C$4,J697:R697)<$C$5,IFJ697: R697=$C$4,J697:R697)))

to give the minimum value in a range with a min in C4 and a max in C5.

However I have a thousand rows. If I select all the column where I want the
results for each row and do control+shift+return, it just copies the formula
with the same row showing rather than the row each formula is locaetd in.
How can I paste a thousand row arrays in one go so they refer to each row?

I would like to have the formula stored in a cell at the top of the column
and then use VBA to populate the column each time I use it and then paste
values over the array so that the spreadhseet is more stable and faster (ie
without one thousand formulae recalculating volatile).

Please could you help

"Mike H" wrote:

Try,

with your lookup value in A2

=MIN(IF(A1:H1=A2,A1:H1))

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

"rupert" wrote:

Hi
Please tell me workaround given my numbers are in ascending order and a need
a minimum.
I have to find the minimum value greater than a number in a row of numbers
placed in ascending order.
The MATCH function requires the numbers to be in descending order with last
parameter of Match set to -1.
Match_type Behavior
1 or omitted MATCH finds the largest value that is less than or equal to
lookup_value. The values in the lookup_array argument must be placed in
ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
0 MATCH finds the first value that is exactly equal to lookup_value. The
values in the lookup_array argument can be in any order.
-1 MATCH finds the smallest value that is greater than or equal to
lookup_value. The values in the lookup_array argument must be placed in
descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and
so on.