Return Value in cell before the largest value

[GA3]

Hi,

I have data in a spreadsheet that has products down the side and weeks of sales across the columns. I need to find the largest week of sales and also the value in the column previous to it. I have been able to use the Large formula to find the largest sales week but I am not able to figure out how to get the value in the column previous to it.

In the example I am able get the value returned for the largest week of sales for Product A,which was 28 (col G), but I need the value of the column before it also which is 4 (in column F). The value in the column to the left of my highest value should be returned in it's own cell.

Can you please help me determine the formula to get the results I need?

Row/Col	A	B	C	D	E	F	G	H	I	J
1				Year >	2017	2017	2017	2017	2017	2017
2				Week # >	30	31	32	33	34	35
3	0012345	Product A	12ct	6pk	6	4	28	18	5	10
4
5

<tbody>
</tbody>

Thank you!

 

[Eric W]

Try:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
Max	value previous column

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Row/Col[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]B[/TD]
[TD="bgcolor: #FAFAFA"]C[/TD]
[TD="bgcolor: #FAFAFA"]D[/TD]
[TD="bgcolor: #FAFAFA"]E[/TD]
[TD="bgcolor: #FAFAFA"]F[/TD]
[TD="bgcolor: #FAFAFA"]G[/TD]
[TD="bgcolor: #FAFAFA"]H[/TD]
[TD="bgcolor: #FAFAFA"]I[/TD]
[TD="bgcolor: #FAFAFA"]J[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Year >[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Week # >[/TD]
[TD="bgcolor: #FAFAFA, align: right"]30[/TD]
[TD="bgcolor: #FAFAFA, align: right"]31[/TD]
[TD="bgcolor: #FAFAFA, align: right"]32[/TD]
[TD="bgcolor: #FAFAFA, align: right"]33[/TD]
[TD="bgcolor: #FAFAFA, align: right"]34[/TD]
[TD="bgcolor: #FAFAFA, align: right"]35[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]12345[/TD]
[TD="bgcolor: #FAFAFA"]Product A[/TD]
[TD="bgcolor: #FAFAFA"]12ct[/TD]
[TD="bgcolor: #FAFAFA"]6pk[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]28[/TD]
[TD="bgcolor: #FAFAFA, align: right"]18[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]4[/TD]

</tbody>

Sheet16​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]M4[/TH]
[TD="align: left"]=MAX(F4:K4)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]N4[/TH]
[TD="align: left"]=INDEX($F4:$K4,MATCH(M4,$F4:$K4,0)-1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[GA3]

Eric, this works perfectly! Thank you so much!

 

[Eric W]

You're welcome!