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

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

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

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 -