Help with Lookup(), VLookup

[Guest]

I have some data in one sheet similar to this:

100.0 2.2
200.0 3.1
300.0 1.9
400.0 2.8

On another sheet, I want to find the maximum value for column 2; i.e. 3.1
(which I can do) and then I want to find the corresponding value in column 1,
i.e. 200.0.

Can someone tell me how to get this?

Thanks,

Phil

 

[T. Valko]

Try this:

=INDEX(A1:A4,MATCH(MAX(B1:B4),B1:B4,0))

Biff

 

[Guest]

With those values in the range D26:E29, I used this formula:

=INDEX(D26:E29,MATCH(MAX(E26:E29),E26:E29,1),1)

Substitue the whole range you're looking at for D26:E29
Substitute the range of the numbers you want the max of for both E26:E29s
Substitue the column within the array for the last 1 in the fomula, if it
isn't the first column (Column D in this case).

 

[Guest]

hi Phil,

assuming there are no error values on data....LOOKUP() may be something like
this..
=LOOKUP(MAX(B1:B4),B1:B4,A1:A4)

regards

 

[T. Valko]

Lookup requires the lookup_vector be sorted ascending. The only reason it
works on this sample is because the the max value is immediately after a
value that is less. Change 2.2 to 3.2 and see what happens.

Biff

 

[Guest]

Phil, pls. disregard my suggestion..

thanks-Valko's right...i forgot that basic lookup reqt..

it is only possible if the lookup range on column B is arranged in order....

regards

 

[Guest]

Phil,
assuming you have long column of data and it so happens that there are two
or more MAX values located somewhere in column B
then - you can also try another way(s) something like this...

to search for the first location on column A adjacent to the first max
valueon column B..
=MIN(IF(MAX(B1:B6)=B1:B6,A1:A6))
press ctrl-shft-ent

search for the first location on column A adjacent to the last max valueon
column B..
=MAX(IF(MAX(B1:B6)=B1:B6,A1:A6))
press ctrl-shft-ent

regards