Max Function Refer To 1st Cell in Row

[jdo23]

I am trying to create a formula that will display the 'name' from column A that corresponds with the row of the value resulting from Max function for range in column B. To illustrate the desired results:

On 'sheet 1' I have the max function determine that cell B5 has the highest number for column B. I want the value in A5 (name associated with the row) to display on Cell C3 of 'Sheet 2'

If that make sense, can someone help?

 

[repairman615]

Hello

Welcome to the Board!!


Try the INDIRECT()


Like:


=INDIRECT("A"&Sheet1!B5)

 

[mikerickson]

perhaps
=INDEX(A:A, MATCH(MAX(B:B), B:B,0),1)

 

[T. Valko]

I am trying to create a formula that will display the 'name' from column A that corresponds with the row of the value resulting from Max function for range in column B. To illustrate the desired results:

On 'sheet 1' I have the max function determine that cell B5 has the highest number for column B. I want the value in A5 (name associated with the row) to display on Cell C3 of 'Sheet 2'

If that make sense, can someone help?

Try this...

Book1
	A	B	C	D	E
2	Name1	33	_	95	Name2
3	Name2	95	_	_	_
4	Name3	24	_	_	_
5	Name4	26	_	_	_
6	Name5	46	_	_	_
7	Name6	89	_	_	_
8	Name7	28	_	_	_
9	Name8	21	_	_	_
10	Name9	74	_	_	_
Sheet1

This formula entered in D2:

=MAX(B2:B10)

This formula entered in E2:

=INDEX(A2:A10,MATCH(D2,B2:B10,0))

 

[repairman615]

I forgot the sheet reference....


=INDIRECT("Sheet1!A"&Sheet1!B5)