Column, Max functions

  • Hi Marielinha and welcome to the board,


    Try a combination of MAX and MATCH.


    <SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"&gt;</SCRIPT&gt;<CENTER&gt;<TABLE cellSpacing=0 cellPadding=0 align=center&gt;<TBODY&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=6&gt;<TABLE width="100%" align=center border=0&gt;<TBODY&gt;<TR&gt;<TD align=left&gt;<FONT color=white&gt;Microsoft Excel - Book2</FONT&gt;</TD&gt;<TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right&gt;___Running: xl2000 : OS = Windows Windows 2000 </FONT&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=6&gt;<TABLE width="100%" align=center border=0 VALIGN="MIDDLE"&gt;<TBODY&gt;<TR&gt;<TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption"&gt;(<U&gt;F</U&gt;)ile (<U&gt;E</U&gt;)dit (<U&gt;V</U&gt;)iew (<U&gt;I</U&gt;)nsert (<U&gt;O</U&gt;)ptions (<U&gt;T</U&gt;)ools (<U&gt;D</U&gt;)ata (<U&gt;W</U&gt;)indow (<U&gt;H</U&gt;)elp <A onclick=show_popup(); href="#javascript:void(0)"&gt;(<U&gt;A</U&gt;)bout</A&gt;</TD&gt;<TD vAlign=center align=right&gt;<FORM name=formCb755237&gt;<INPUT onclick='window.clipboardData.setData("Text",document.formFb078704.sltNb935705.value);' type=button value="Copy Formula" name=btCb873980&gt;</FORM&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=6&gt;<TABLE border=0&gt;<TBODY&gt;<TR&gt;<FORM name=formFb078704&gt;<TD style="WIDTH: 60px" align=middle bgColor=white&gt;<SELECT onchange="document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value" name=sltNb935705&gt;<OPTION value==MATCH(MAX(A1:E1),A1:E1,0) selected&gt;A2</OPTION&gt;</SELECT&gt;</TD&gt;<TD align=right width="3%" bgColor=#d4d0c8&gt;<B>=</B&gt;</TD&gt;<TD align=left bgColor=white&gt;<INPUT size=80 value==MATCH(MAX(A1:E1),A1:E1,0) name=txbFb426622&gt;</TD&gt;</FORM&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<BR&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;A</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;B</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;C</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;D</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;E</CENTER&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<CENTER&gt;1</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;44.59429</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;81.63074</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;16.61393</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;86.58137</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;36.78623</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<CENTER&gt;2</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;<A onclick="document.formFb078704.sltNb935705.options[0].selected=true; document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value;" href="#javascript:void(0);"&gt;4</FONT&gt;</A&gt;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" colSpan=6&gt;<TABLE width="100%" align=left VALIGN="TOP"&gt;<TBODY&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; WIDTH: 120pt; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff" align=left&gt;<U&gt;Sheet1</U&gt;</TD&gt;<TD&gt;&nbsp;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;<BR&gt;<FONT color=#339966 size=1&gt;[HtmlMaker 2.41] </FONT&gt;<FONT color=#339966 size=1&gt;To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT&gt;<BR&gt;<FONT color=red size=1&gt;PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</FONT&gt;</CENTER&gt;
    Cheers
    Andy

  • Hi Marielinha,


    Andy beat me to the answer, but I had something to add. If you data does not start in Col A, you have to add the appropriate column offset number to the MATCH Statement.


    example - if you data starts in Col C you could use:


    =MATCH(MAX(C1:G1),C1:G1,0) + 2


    and you would get the answer 6 from Andy's data which would be the correct id for Col F.


    .....Ralph

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!