| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Formula Using Max Match Logic
I have the following spreadsheet: r1 mar mar mar apr apr r2 apr 20 25 30 40 50 r3 mar 50 20 25 30 15 r1 is cel a1, in (cel b2) formula resides, I want in cel b2 the corresponding month in row 1 with the highest value, in r2 the highest value is 50(cel g2), so I want the name in cel g1 to appear in cel b2. In r3 the highest value is 50, so I want the formula to return the name mar which is in cel c1. What is the best formula to use, thanks. -- JR573PUTT ------------------------------------------------------------------------ JR573PUTT's Profile: http://www.excelforum.com/member.php...o&userid=31587 View this thread: http://www.excelforum.com/showthread...hreadid=516375 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Array Formula Using Max Match Logic
JR573PUTT,
=INDEX($B$1:$G$1,MATCH(MAX(B2:G2),B2:G2,FALSE)) Change the G's to your actual last column. Copy down for one row. HTH, Bernie MS Excel MVP "JR573PUTT" wrote in message ... I have the following spreadsheet: r1 mar mar mar apr apr r2 apr 20 25 30 40 50 r3 mar 50 20 25 30 15 r1 is cel a1, in (cel b2) formula resides, I want in cel b2 the corresponding month in row 1 with the highest value, in r2 the highest value is 50(cel g2), so I want the name in cel g1 to appear in cel b2. In r3 the highest value is 50, so I want the formula to return the name mar which is in cel c1. What is the best formula to use, thanks. -- JR573PUTT ------------------------------------------------------------------------ JR573PUTT's Profile: http://www.excelforum.com/member.php...o&userid=31587 View this thread: http://www.excelforum.com/showthread...hreadid=516375 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Array Formula Using Max Match Logic
Hi JR573PUTT,
another formula is =INDIRECT(ADDRESS(1,MATCH(MAX(C2:G2),C2:G2,FALSE)+ COLUMN())) and I think Bernie's formula should be =INDEX($C$1:$G$1,MATCH(MAX(C2:G2),C2:G2,FALSE)) Bernie's is better since it involves fewer function calls Ken Johnson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Array Formula Using Max Match Logic
Thank you both formulas work fine, thanks again. -- JR573PUTT ------------------------------------------------------------------------ JR573PUTT's Profile: http://www.excelforum.com/member.php...o&userid=31587 View this thread: http://www.excelforum.com/showthread...hreadid=516375 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Array Formula Using Max Match Logic
Hi JR573PUTT,
You're welcome, thanks for the feedback. Ken Johnson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help with formula statament with a MATCH. | Excel Worksheet Functions | |||
| Array | Excel Worksheet Functions | |||
| Simplifying array formula which evaluates as error. | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| How to add in an array formula if iisna index match | Excel Worksheet Functions | |||
Hybrid Mode
