Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
displaying adjacent cell when using max/min formulae
I'm using the max/min formulas to display the appropriate values [say in
column A]. say the maximum is located in cell A20, i'd also like to have excel display the output of cell B20 in another cell. how do i do this in Excel 2003? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
displaying adjacent cell when using max/min formulae
Hi,
=VLOOKUP(MAX(A1:A50),A1:B50,2,FALSE) Mike "formula428" wrote: I'm using the max/min formulas to display the appropriate values [say in column A]. say the maximum is located in cell A20, i'd also like to have excel display the output of cell B20 in another cell. how do i do this in Excel 2003? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
displaying adjacent cell when using max/min formulae
=INDEX(B:B,MATCH(MAX(A:A),A:A,0))
-- Regards, Peo Sjoblom "formula428" wrote in message ... I'm using the max/min formulas to display the appropriate values [say in column A]. say the maximum is located in cell A20, i'd also like to have excel display the output of cell B20 in another cell. how do i do this in Excel 2003? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
displaying adjacent cell when using max/min formulae
Does it matter if the "output cell" is text?
Maybe I should give the actual columns in the example. I am doing max/min for column E. I want excel to give me the corresponding text of column C. Therefore, if the max is E356, I want (in another cell) Excel to display C356 which is text, not a value. "Mike H" wrote: Hi, =VLOOKUP(MAX(A1:A50),A1:B50,2,FALSE) Mike "formula428" wrote: I'm using the max/min formulas to display the appropriate values [say in column A]. say the maximum is located in cell A20, i'd also like to have excel display the output of cell B20 in another cell. how do i do this in Excel 2003? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
displaying adjacent cell when using max/min formulae
Hi,
you could use =VLOOKUP(MAX(A1:A24),A1:B24,2,) -- Thanks, Shane Devenshire "formula428" wrote: I'm using the max/min formulas to display the appropriate values [say in column A]. say the maximum is located in cell A20, i'd also like to have excel display the output of cell B20 in another cell. how do i do this in Excel 2003? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
displaying adjacent cell when using max/min formulae
Use the index function suggested by Peo
"formula428" wrote: Does it matter if the "output cell" is text? Maybe I should give the actual columns in the example. I am doing max/min for column E. I want excel to give me the corresponding text of column C. Therefore, if the max is E356, I want (in another cell) Excel to display C356 which is text, not a value. "Mike H" wrote: Hi, =VLOOKUP(MAX(A1:A50),A1:B50,2,FALSE) Mike "formula428" wrote: I'm using the max/min formulas to display the appropriate values [say in column A]. say the maximum is located in cell A20, i'd also like to have excel display the output of cell B20 in another cell. how do i do this in Excel 2003? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
displaying adjacent cell when using max/min formulae
I couldn't get VLOOKUP to work, but index worked great! thanks!
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
displaying adjacent cell when using max/min formulae
Hi,
My initial response was to your first posting, when I revisited you actual layout, given in the 2nd posting, you are correct, VLOOKUP won't work because it needs to check the left hand column and return data in that column or one to the right. And you are look in column E and wanting to return something from column C. However, you can do that with a related function, LOOKUP, provided your data is ordered, which it probalby not the case: =LOOKUP(MAX(E1:E400),E1:E400,C1:C400) but this only works if column E is sorted ascending. So I think you should use the MATCH, OFFSET or INDEX combinations. -- Thanks, Shane Devenshire "formula428" wrote: I couldn't get VLOOKUP to work, but index worked great! thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I stop text displaying in blank adjacent cells? | Excel Discussion (Misc queries) | |||
Displaying formulae changes formatting | Excel Worksheet Functions | |||
Excel displaying formulae as constant and not calculating formula | Excel Worksheet Functions | |||
Displaying contents of adjacent cells. | Excel Discussion (Misc queries) | |||
Excel 2002: I get formulae displaying in cells rather than value | Excel Worksheet Functions |