Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LOOKUP is working - but VLOOKUP is not | Excel Discussion (Misc queries) | |||
Vlookup. Match. Lookup. Wtf? | Excel Discussion (Misc queries) | |||
Double lookup without using vlookup? | Excel Worksheet Functions | |||
IF, VLOOKUP & LOOKUP TABLE ON OTHER SHEET | Excel Discussion (Misc queries) | |||
Getting #N/A from Vlookup when matching value exist in the lookup data range. | Excel Worksheet Functions |