MixedUpExcel
Board Regular
- Joined
- Apr 7, 2015
- Messages
- 222
- Office Version
- 365
- Platform
- Windows
Hi All,
I can't quite get the 2 bits of formula to blend together.. any ideas?
i have a table of values with Months across the top.
In my example, I want to find the largest value for the current Month - the Month name 'NOV' is in a separate cell (B16) and changes each month.
[TABLE="width: 960"]
<colgroup><col width="64" style="width: 48pt;"><col width="64" span="14" style="width: 48pt;"></colgroup><tbody>[TR]
[TD="class: xl79, width: 64"][/TD]
[TD="class: xl79, width: 64"]B[/TD]
[TD="class: xl79, width: 64"]C[/TD]
[TD="class: xl79, width: 64"]D[/TD]
[TD="class: xl79, width: 64"]E[/TD]
[TD="class: xl79, width: 64"]F[/TD]
[TD="class: xl79, width: 64"]G[/TD]
[TD="class: xl79, width: 64"]H[/TD]
[TD="class: xl79, width: 64"]I[/TD]
[TD="class: xl79, width: 64"]J[/TD]
[TD="class: xl79, width: 64"]K[/TD]
[TD="class: xl79, width: 64"]L[/TD]
[TD="class: xl79, width: 64"]M[/TD]
[TD="class: xl79, width: 64"]N[/TD]
[TD="class: xl79, width: 64"]O[/TD]
[/TR]
[TR]
[TD="class: xl79"]2[/TD]
[TD][/TD]
[TD="class: xl78"]MONTH[/TD]
[TD="class: xl79"][/TD]
[TD="class: xl79"][/TD]
[TD="class: xl79"][/TD]
[TD="class: xl79"][/TD]
[TD="class: xl79"][/TD]
[TD="class: xl79"]I[/TD]
[TD="class: xl79"][/TD]
[TD="class: xl79"][/TD]
[TD="class: xl79"][/TD]
[TD="class: xl79"][/TD]
[TD="class: xl79"][/TD]
[TD="class: xl79"][/TD]
[/TR]
[TR]
[TD="class: xl79"]3[/TD]
[TD="class: xl84"]name[/TD]
[TD="class: xl80"]JAN[/TD]
[TD="class: xl81"]FEB[/TD]
[TD="class: xl81"]MAR[/TD]
[TD="class: xl81"]APR[/TD]
[TD="class: xl81"]MAY[/TD]
[TD="class: xl81"]JUN[/TD]
[TD="class: xl81"]JUL[/TD]
[TD="class: xl81"]AUG[/TD]
[TD="class: xl81"]SEP[/TD]
[TD="class: xl81"]OCT[/TD]
[TD="class: xl81"]NOV[/TD]
[TD="class: xl82"]DEC[/TD]
[TD="class: xl83"]TOTAL[/TD]
[/TR]
[TR]
[TD="class: xl79"]4[/TD]
[TD="class: xl85"]andrew[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]55[/TD]
[TD="class: xl67"]55[/TD]
[TD="class: xl67"]10[/TD]
[TD="class: xl68"]55[/TD]
[TD="class: xl75"] [/TD]
[/TR]
[TR]
[TD="class: xl79"]5[/TD]
[TD="class: xl86"]brian[/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"]55[/TD]
[TD="class: xl70"]55[/TD]
[TD="class: xl70"]20[/TD]
[TD="class: xl71"]55[/TD]
[TD="class: xl76"] [/TD]
[/TR]
[TR]
[TD="class: xl79"]6[/TD]
[TD="class: xl86"]craig[/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"]55[/TD]
[TD="class: xl70"]55[/TD]
[TD="class: xl70"]30[/TD]
[TD="class: xl71"]55[/TD]
[TD="class: xl76"] [/TD]
[/TR]
[TR]
[TD="class: xl79"]7[/TD]
[TD="class: xl86"]daneil[/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"]55[/TD]
[TD="class: xl70"]55[/TD]
[TD="class: xl70"]40[/TD]
[TD="class: xl71"]55[/TD]
[TD="class: xl76"] [/TD]
[/TR]
[TR]
[TD="class: xl79"]8[/TD]
[TD="class: xl86"]edward[/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"]55[/TD]
[TD="class: xl70"]55[/TD]
[TD="class: xl70"]50[/TD]
[TD="class: xl71"]55[/TD]
[TD="class: xl76"] [/TD]
[/TR]
[TR]
[TD="class: xl79"]9[/TD]
[TD="class: xl86"]freddie[/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"]55[/TD]
[TD="class: xl70"]55[/TD]
[TD="class: xl70"]100[/TD]
[TD="class: xl71"]55[/TD]
[TD="class: xl76"] [/TD]
[/TR]
[TR]
[TD="class: xl79"]10[/TD]
[TD="class: xl86"]geoff[/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"]55[/TD]
[TD="class: xl70"]55[/TD]
[TD="class: xl70"]90[/TD]
[TD="class: xl71"]55[/TD]
[TD="class: xl76"] [/TD]
[/TR]
[TR]
[TD="class: xl79"]11[/TD]
[TD="class: xl86"]harry[/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"]55[/TD]
[TD="class: xl70"]55[/TD]
[TD="class: xl70"]80[/TD]
[TD="class: xl71"]55[/TD]
[TD="class: xl76"] [/TD]
[/TR]
[TR]
[TD="class: xl79"]12[/TD]
[TD="class: xl87"]ian[/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl73"] [/TD]
[TD="class: xl73"] [/TD]
[TD="class: xl73"] [/TD]
[TD="class: xl73"] [/TD]
[TD="class: xl73"] [/TD]
[TD="class: xl73"] [/TD]
[TD="class: xl73"] [/TD]
[TD="class: xl73"]55[/TD]
[TD="class: xl73"]55[/TD]
[TD="class: xl73"]70[/TD]
[TD="class: xl74"]55[/TD]
[TD="class: xl77"] [/TD]
[/TR]
[TR]
[TD="class: xl79"]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl79"]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl79"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl79"]16[/TD]
[TD="class: xl88"]NOV[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl79"]17[/TD]
[TD="class: xl89"]100[/TD]
[TD="colspan: 6"]=LARGE(INDEX($C$4:$N$12,,MATCH($B$16,$C$3:$N$3,0)),1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl79"]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl79"]19[/TD]
[TD="class: xl65"]freddie[/TD]
[TD="colspan: 5"]=INDEX($B$4:$B$12,MATCH(B17,$M$4:$M$12,0))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl79"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The formula I have in Cell B17 is currently shown in Cell C17 which calculates '100'
I then want to pull back the name into Cell B19 - 'freddie'.
The problem I have is that if I use the formula:
this depends on me knowing which column to look for the value 100.
I want to be able to pull back the result of 'freddie' (or whatever matches the largest value for that particular month) by being able to use the month in Cell B16 to determine which column to look for the highest value.
Any suggestions would be appreciated.
Thanks in advance.
Simon
I can't quite get the 2 bits of formula to blend together.. any ideas?
i have a table of values with Months across the top.
In my example, I want to find the largest value for the current Month - the Month name 'NOV' is in a separate cell (B16) and changes each month.
[TABLE="width: 960"]
<colgroup><col width="64" style="width: 48pt;"><col width="64" span="14" style="width: 48pt;"></colgroup><tbody>[TR]
[TD="class: xl79, width: 64"][/TD]
[TD="class: xl79, width: 64"]B[/TD]
[TD="class: xl79, width: 64"]C[/TD]
[TD="class: xl79, width: 64"]D[/TD]
[TD="class: xl79, width: 64"]E[/TD]
[TD="class: xl79, width: 64"]F[/TD]
[TD="class: xl79, width: 64"]G[/TD]
[TD="class: xl79, width: 64"]H[/TD]
[TD="class: xl79, width: 64"]I[/TD]
[TD="class: xl79, width: 64"]J[/TD]
[TD="class: xl79, width: 64"]K[/TD]
[TD="class: xl79, width: 64"]L[/TD]
[TD="class: xl79, width: 64"]M[/TD]
[TD="class: xl79, width: 64"]N[/TD]
[TD="class: xl79, width: 64"]O[/TD]
[/TR]
[TR]
[TD="class: xl79"]2[/TD]
[TD][/TD]
[TD="class: xl78"]MONTH[/TD]
[TD="class: xl79"][/TD]
[TD="class: xl79"][/TD]
[TD="class: xl79"][/TD]
[TD="class: xl79"][/TD]
[TD="class: xl79"][/TD]
[TD="class: xl79"]I[/TD]
[TD="class: xl79"][/TD]
[TD="class: xl79"][/TD]
[TD="class: xl79"][/TD]
[TD="class: xl79"][/TD]
[TD="class: xl79"][/TD]
[TD="class: xl79"][/TD]
[/TR]
[TR]
[TD="class: xl79"]3[/TD]
[TD="class: xl84"]name[/TD]
[TD="class: xl80"]JAN[/TD]
[TD="class: xl81"]FEB[/TD]
[TD="class: xl81"]MAR[/TD]
[TD="class: xl81"]APR[/TD]
[TD="class: xl81"]MAY[/TD]
[TD="class: xl81"]JUN[/TD]
[TD="class: xl81"]JUL[/TD]
[TD="class: xl81"]AUG[/TD]
[TD="class: xl81"]SEP[/TD]
[TD="class: xl81"]OCT[/TD]
[TD="class: xl81"]NOV[/TD]
[TD="class: xl82"]DEC[/TD]
[TD="class: xl83"]TOTAL[/TD]
[/TR]
[TR]
[TD="class: xl79"]4[/TD]
[TD="class: xl85"]andrew[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]55[/TD]
[TD="class: xl67"]55[/TD]
[TD="class: xl67"]10[/TD]
[TD="class: xl68"]55[/TD]
[TD="class: xl75"] [/TD]
[/TR]
[TR]
[TD="class: xl79"]5[/TD]
[TD="class: xl86"]brian[/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"]55[/TD]
[TD="class: xl70"]55[/TD]
[TD="class: xl70"]20[/TD]
[TD="class: xl71"]55[/TD]
[TD="class: xl76"] [/TD]
[/TR]
[TR]
[TD="class: xl79"]6[/TD]
[TD="class: xl86"]craig[/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"]55[/TD]
[TD="class: xl70"]55[/TD]
[TD="class: xl70"]30[/TD]
[TD="class: xl71"]55[/TD]
[TD="class: xl76"] [/TD]
[/TR]
[TR]
[TD="class: xl79"]7[/TD]
[TD="class: xl86"]daneil[/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"]55[/TD]
[TD="class: xl70"]55[/TD]
[TD="class: xl70"]40[/TD]
[TD="class: xl71"]55[/TD]
[TD="class: xl76"] [/TD]
[/TR]
[TR]
[TD="class: xl79"]8[/TD]
[TD="class: xl86"]edward[/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"]55[/TD]
[TD="class: xl70"]55[/TD]
[TD="class: xl70"]50[/TD]
[TD="class: xl71"]55[/TD]
[TD="class: xl76"] [/TD]
[/TR]
[TR]
[TD="class: xl79"]9[/TD]
[TD="class: xl86"]freddie[/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"]55[/TD]
[TD="class: xl70"]55[/TD]
[TD="class: xl70"]100[/TD]
[TD="class: xl71"]55[/TD]
[TD="class: xl76"] [/TD]
[/TR]
[TR]
[TD="class: xl79"]10[/TD]
[TD="class: xl86"]geoff[/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"]55[/TD]
[TD="class: xl70"]55[/TD]
[TD="class: xl70"]90[/TD]
[TD="class: xl71"]55[/TD]
[TD="class: xl76"] [/TD]
[/TR]
[TR]
[TD="class: xl79"]11[/TD]
[TD="class: xl86"]harry[/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"]55[/TD]
[TD="class: xl70"]55[/TD]
[TD="class: xl70"]80[/TD]
[TD="class: xl71"]55[/TD]
[TD="class: xl76"] [/TD]
[/TR]
[TR]
[TD="class: xl79"]12[/TD]
[TD="class: xl87"]ian[/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl73"] [/TD]
[TD="class: xl73"] [/TD]
[TD="class: xl73"] [/TD]
[TD="class: xl73"] [/TD]
[TD="class: xl73"] [/TD]
[TD="class: xl73"] [/TD]
[TD="class: xl73"] [/TD]
[TD="class: xl73"]55[/TD]
[TD="class: xl73"]55[/TD]
[TD="class: xl73"]70[/TD]
[TD="class: xl74"]55[/TD]
[TD="class: xl77"] [/TD]
[/TR]
[TR]
[TD="class: xl79"]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl79"]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl79"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl79"]16[/TD]
[TD="class: xl88"]NOV[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl79"]17[/TD]
[TD="class: xl89"]100[/TD]
[TD="colspan: 6"]=LARGE(INDEX($C$4:$N$12,,MATCH($B$16,$C$3:$N$3,0)),1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl79"]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl79"]19[/TD]
[TD="class: xl65"]freddie[/TD]
[TD="colspan: 5"]=INDEX($B$4:$B$12,MATCH(B17,$M$4:$M$12,0))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl79"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The formula I have in Cell B17 is currently shown in Cell C17 which calculates '100'
Code:
=LARGE(INDEX($C$4:$N$12,,MATCH($B$16,$C$3:$N$3,0)),1)
I then want to pull back the name into Cell B19 - 'freddie'.
The problem I have is that if I use the formula:
Code:
=INDEX($B$4:$B$12,MATCH(B17,$M$4:$M$12,0))
this depends on me knowing which column to look for the value 100.
I want to be able to pull back the result of 'freddie' (or whatever matches the largest value for that particular month) by being able to use the month in Cell B16 to determine which column to look for the highest value.
Any suggestions would be appreciated.
Thanks in advance.
Simon