Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Row in a named range
I have a named range that contains several rows and columns of data. I'm
using the following formula to determine the minimum value in a range that consists of all but the last two rows in the second column. =MIN(OFFSET(DataRange,0,1,COUNT(INDEX(DataRange,0, 1,1))-2,1)) This formula gives me the result I want. But, now I need to know how to find the value in row three that corresponds to value from the above formula? I'm assuming I need to find the row and then get the value based on the row and column, but my attempts have not been successful. Thanks, Raul |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Row in a named range
Have a look at INDEX and MATCH in Excel Help.
Pete On Nov 23, 10:46 pm, Raul wrote: I have a named range that contains several rows and columns of data. I'm using the following formula to determine the minimum value in a range that consists of all but the last two rows in the second column. =MIN(OFFSET(DataRange,0,1,COUNT(INDEX(DataRange,0, 1,1))-2,1)) This formula gives me the result I want. But, now I need to know how to find the value in row three that corresponds to value from the above formula? I'm assuming I need to find the row and then get the value based on the row and column, but my attempts have not been successful. Thanks, Raul |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Row in a named range
Thanks for the tip. I came up with following based on your input:
=INDEX(DataRange,MATCH(MIN(OFFSET(DataRange,0,1,CO UNT(INDEX(DataRange,0,1,1))-2,1)),OFFSET(DataRange,0,1,COUNT(INDEX(DataRange,0 ,1,1))-2,1),0),3,1) Thanks again, Raul "Pete_UK" wrote: Have a look at INDEX and MATCH in Excel Help. Pete On Nov 23, 10:46 pm, Raul wrote: I have a named range that contains several rows and columns of data. I'm using the following formula to determine the minimum value in a range that consists of all but the last two rows in the second column. =MIN(OFFSET(DataRange,0,1,COUNT(INDEX(DataRange,0, 1,1))-2,1)) This formula gives me the result I want. But, now I need to know how to find the value in row three that corresponds to value from the above formula? I'm assuming I need to find the row and then get the value based on the row and column, but my attempts have not been successful. Thanks, Raul |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Row in a named range
You're welcome - glad you were able to get something to work.
Pete On Nov 24, 12:31 am, Raul wrote: Thanks for the tip. I came up with following based on your input: =INDEX(DataRange,MATCH(MIN(OFFSET(DataRange,0,1,CO UNT(INDEX(DataRange,0,1,1-))-2,1)),OFFSET(DataRange,0,1,COUNT(INDEX(DataRange,0 ,1,1))-2,1),0),3,1) Thanks again, Raul "Pete_UK" wrote: Have a look at INDEX and MATCH in Excel Help. Pete On Nov 23, 10:46 pm, Raul wrote: I have a named range that contains several rows and columns of data. I'm using the following formula to determine the minimum value in a range that consists of all but the last two rows in the second column. =MIN(OFFSET(DataRange,0,1,COUNT(INDEX(DataRange,0, 1,1))-2,1)) This formula gives me the result I want. But, now I need to know how to find the value in row three that corresponds to value from the above formula? I'm assuming I need to find the row and then get the value based on the row and column, but my attempts have not been successful. Thanks, Raul- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Cell in Named Range | Excel Worksheet Functions | |||
VBA: find number of columns in named range? | Excel Discussion (Misc queries) | |||
Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
How to find all formulas that used a certain named range | Excel Discussion (Misc queries) |