Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hlookup/max value?
Hi, What I am trying to do is look for the max value in a row and return the relevant column header Example: 1-----2-----3-----4------5-----6-----7---- 35---29----16----3----21----45----5---- So, the function should look down a row, find the max value of 45 and return the column header of 6. I have been tryinga combination of hlookup and max value but haven't been able to product the desired results. Any help would be greatly appreciated. swjtx -- swjtx ------------------------------------------------------------------------ swjtx's Profile: http://www.excelforum.com/member.php...o&userid=29716 View this thread: http://www.excelforum.com/showthread...hreadid=494418 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hlookup/max value?
Hi!
Try this: =INDEX(A1:A7,MATCH(MAX(B1:B7),B1:B7,0)) Where A1:A7 are the headers, B1:B7 ae the numeric values. NB: if there are multiple instances of the max value the formula will return the corresponding header of the first instance. Biff "swjtx" wrote in message ... Hi, What I am trying to do is look for the max value in a row and return the relevant column header Example: 1-----2-----3-----4------5-----6-----7---- 35---29----16----3----21----45----5---- So, the function should look down a row, find the max value of 45 and return the column header of 6. I have been tryinga combination of hlookup and max value but haven't been able to product the desired results. Any help would be greatly appreciated. swjtx -- swjtx ------------------------------------------------------------------------ swjtx's Profile: http://www.excelforum.com/member.php...o&userid=29716 View this thread: http://www.excelforum.com/showthread...hreadid=494418 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hlookup/max value?
Ooops!
=INDEX(A1:A7,MATCH(MAX(B1:B7),B1:B7,0)) Should be: =INDEX(A1:G1,MATCH(MAX(A2:G2),A2:G2,0)) Where A1:G1 are the headers, A2:G2 ae the numeric values. Biff "Biff" wrote in message ... Hi! Try this: =INDEX(A1:A7,MATCH(MAX(B1:B7),B1:B7,0)) Where A1:A7 are the headers, B1:B7 ae the numeric values. NB: if there are multiple instances of the max value the formula will return the corresponding header of the first instance. Biff "swjtx" wrote in message ... Hi, What I am trying to do is look for the max value in a row and return the relevant column header Example: 1-----2-----3-----4------5-----6-----7---- 35---29----16----3----21----45----5---- So, the function should look down a row, find the max value of 45 and return the column header of 6. I have been tryinga combination of hlookup and max value but haven't been able to product the desired results. Any help would be greatly appreciated. swjtx -- swjtx ------------------------------------------------------------------------ swjtx's Profile: http://www.excelforum.com/member.php...o&userid=29716 View this thread: http://www.excelforum.com/showthread...hreadid=494418 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hlookup/max value?
Hi and Thanks! It worked like a charm. I inserted the $ so I could drag the formula down without changing the range (a tip from a different person on this site) like this: =INDEX(A1:G1,MATCH(MAX($A$2:$G$2),$A$2:$G$2,0)) swjtx -- swjtx ------------------------------------------------------------------------ swjtx's Profile: http://www.excelforum.com/member.php...o&userid=29716 View this thread: http://www.excelforum.com/showthread...hreadid=494418 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hlookup/max value?
Biff to the rescue, again!!! :) Neil M |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|