VLOOKUP with MAX criteria
 

Struggling with this one:

Need to return a value in column D that cooresponds to the changing MAX
result in column E. In the example following, the returned result would
be 82:

(B) (C) (D) (E)
1 0 82 154
2 82 102 3
3 102 128 3
4 128 160 4
5 160 200 3


Using the following syntax (which is incorrect):

=VLOOKUP(MAX(E3:E7),B3:E7,3)

Can I get the (D) result returned if any of the 5 sub's (B) yield a MAX
in (E)?

Thanks in advance for your help!


--
tralls
------------------------------------------------------------------------
tralls's Profile: http://www.excelforum.com/member.php...nfo&userid=859
View this thread: http://www.excelforum.com/showthread...hreadid=479275

VLOOKUP with MAX criteria
 

Hi Tralls,

Vlookup return only to the right. Try this.

From Chip Pearson site with the max function added.
http://www.cpearson.com/excel/lookups.htm#LeftLookup

=OFFSET(E1,MATCH(MAX(E1:E5),E1:E5,0)-1,-1,1,1)

HTH
Regards,
Howard

"tralls" wrote in
message ...

Struggling with this one:

Need to return a value in column D that cooresponds to the changing MAX
result in column E. In the example following, the returned result would
be 82:

(B) (C) (D) (E)
1 0 82 154
2 82 102 3
3 102 128 3
4 128 160 4
5 160 200 3


Using the following syntax (which is incorrect):

=VLOOKUP(MAX(E3:E7),B3:E7,3)

Can I get the (D) result returned if any of the 5 sub's (B) yield a MAX
in (E)?

Thanks in advance for your help!


--
tralls
------------------------------------------------------------------------
tralls's Profile:
http://www.excelforum.com/member.php...nfo&userid=859
View this thread: http://www.excelforum.com/showthread...hreadid=479275

VLOOKUP with MAX criteria
 

Try...

=INDEX(D3:D7,MATCH(MAX(E3:E7),E3:E7,0))

Hope this helps!

In article ,
tralls wrote:

Struggling with this one:

Need to return a value in column D that cooresponds to the changing MAX
result in column E. In the example following, the returned result would
be 82:

(B) (C) (D) (E)
1 0 82 154
2 82 102 3
3 102 128 3
4 128 160 4
5 160 200 3


Using the following syntax (which is incorrect):

=VLOOKUP(MAX(E3:E7),B3:E7,3)

Can I get the (D) result returned if any of the 5 sub's (B) yield a MAX
in (E)?

Thanks in advance for your help!