Help with Lookup(), VLookup
 

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

Help with Lookup(), VLookup
 

Try this:

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

Biff

"Phil" wrote in message
...
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

Help with Lookup(), VLookup
 

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).

"Phil" wrote:

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

Help with Lookup(), VLookup
 

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
--
*****
birds of the same feather flock together..



"Phil" wrote:

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

Help with Lookup(), VLookup
 

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

"driller" wrote in message
...
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
--
*****
birds of the same feather flock together..



"Phil" wrote:

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

Help with Lookup(), VLookup
 

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
--
*****
birds of the same feather flock together..



"T. Valko" wrote:

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

"driller" wrote in message
...
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
--
*****
birds of the same feather flock together..



"Phil" wrote:

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

Help with Lookup(), VLookup
 

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
--
*****
birds of the same feather flock together..



"T. Valko" wrote:

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

"driller" wrote in message
...
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
--
*****
birds of the same feather flock together..



"Phil" wrote:

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