OFFSET embeded with MAX

[nicolehalliday]

Hi everyone,
I am searching a column range of values to return the maximum value, which I did simply by using the MAX function [=MAX(D2:D501)]. My next step is to return the value of the cell three cells left of the maximum value, which is a stock name. Basically, I would like one cell to return which stock in the range has the highest value, and another cell to return that specific stocks value. I tried this but it is not working:

=OFFSET(MAX(D2:D501),,-3)

Thanks in advance for you time and help!!!

 

[T. Valko]

Hi everyone,
I am searching a column range of values to return the maximum value, which I did simply by using the MAX function [=MAX(D2:D501)]. My next step is to return the value of the cell three cells left of the maximum value, which is a stock name. Basically, I would like one cell to return which stock in the range has the highest value, and another cell to return that specific stocks value. I tried this but it is not working:

=OFFSET(MAX(D2:D501),,-3)

Thanks in advance for you time and help!!!

Try it like this...

=INDEX(A2:A501,MATCH(MAX(D2:D501),D2:D501,0))

 

[snoopyhr]

Here is my 2 cents on this...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Stock Name			Value		Value	Returned item 3 columns to the left of lookup column
2	Name-8			1		343	Name-7
3	Name-2			2
4	Name-3			3
5	Name-7			4
6	Name-4			56
7	Name-2			7
8	Name-10			2
9	Name-7			343
10	Name-1			68
11	Name-7			6
12	Name-9			23
13	Name-4			11
14	Name-10			67
15	Name-7			4
16	Name-5			3
17	Name-2			9
18
Sheet1

 

[Aladin Akyurek]

Hi everyone,
I am searching a column range of values to return the maximum value, which I did simply by using the MAX function [=MAX(D2:D501)]. My next step is to return the value of the cell three cells left of the maximum value, which is a stock name. Basically, I would like one cell to return which stock in the range has the highest value, and another cell to return that specific stocks value. I tried this but it is not working:

=OFFSET(MAX(D2:D501),,-3)

Thanks in advance for you time and help!!!

E1: Max

E2:

=MAX(D2:D501)

E3:

=COUNTIF(D2:D501,E2)

E4: Stock

E5, control+shift+enter, not just enter, and copy down:

=IF(ROWS($E$5:E5)<=$E$3,INDEX($A$2:$A$501,SMALL(IF($D$2:$D$501=$E$2,
     ROW($D$2:$D$501)-ROW($D$2)+1),ROWS($E$5:E5))),"")

 

[nicolehalliday]

I had no idea it could be done in so many ways but all of them worked!!! Thanks very much for all of your help.

 

[Aladin Akyurek]

I had no idea it could be done in so many ways but all of them worked!!! Thanks very much for all of your help.

They are not equivalent though...

 

[T. Valko]

I had no idea it could be done in so many ways but all of them worked!!! Thanks very much for all of your help.

You're welcome. Thanks for the feedback!