Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can we get unique values in match function for same match key. | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
Match as well as does not match array function | Excel Discussion (Misc queries) | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions |