Find row referenced by a formula

[Armando]

Hello this is going to sound like a lame question but I was wondering is
there is a way to get the row from a formula. For example I am using the
large formula to find the second largest value in a range, I would like to be
able to find the row of the value that the large formula returns. Is there
anyway to do this. Thanks.

 

[merjet]

Suppose the range is A3:A8 and the LARGE formula is in B3.
Then =MATCH(B3,A3:A8,0)+ROW(A3)-1 would give the row
number in col A of the value in B3.

Hth,
Merjet

 

[Armando]

Suppose the range is A3:A8 and the LARGE formula is in B3.
Then =MATCH(B3,A3:A8,0)+ROW(A3)-1 would give the row
number in col A of the value in B3.

Hth,
Merjet

Thank. I just tried it and it worked like a charm. Thanks a million merjet your great.

 

[Armando]

Suppose the range is A3:A8 and the LARGE formula is in B3.
Then =MATCH(B3,A3:A8,0)+ROW(A3)-1 would give the row
number in col A of the value in B3.

Hth,
Merjet

Sorry to ask another dumb question but is there anyway to create a cell reference using that row value. Example. Once I get the row number I want to create a formula in a cell that takes the row value to reference a cell. =A(row value) or =B(Row value) . Thanks again for all your help.

 

[merjet]

Sorry to ask another dumb question but is there anyway

to create a cell reference using that row value. Example.
Once I get the row number I want to create a formula in a
cell that takes the row value to reference a cell.
=A(row value) or =B(Row value) .

Sorry; that's not clear to me. Maybe a more specific
example would help.

Merjet

 

[Armando]

Sorry. After finding which row holds the greatest value I would like to
create another formula that can pull more information using that row value. I
was wondering if there is a way to concatenate a column letter with the row
value inside of a formula. Example. using your Match formula we find that the
row with the greatest value is row 4. I would like to create a formula that
looks at that value and uses that row number to pull more information from
that row. The column may change but the row referenced will be the value that
the Match formula found. I am sorry about how confusing this is but it is
kind of hard to explain. Thanks.

 

[merjet]

It's still not clear to me. But have a look at the
INDEX formula. It may help each your goal.

Hth,
Merjet