Find maximum values in table and return values from different columns in the same table.

[Munch91]

I have the following dummy table from which I need to determine two different sets of maximum value and their corresponding information.

The first set of values I need to find is the highest value in Column S "Store" of the table and return that value into Cell V2. If there are multiple entries of the same value, then it needs to be the most recent of those according to the date in Column A "Date" of the table. Cells V3 & V4 need to provide the corresponding Date and Day value from the row that the highest value is located in respectively. So for the example table below it needs to return the following:

Cell V2: 60
Cell V3: 12-Feb-59
Cell V4: Wednesday

The second set of values is a bit more complicated. I need to look at all of the tabled values in Columns D-R as a whole ("Freezer" - "Aisle 14") and find the highest single value out of all of them and return that value in Cell V6, again if there are multiple values all the same, then it needs to be the most recent value according to the date in Column A "Date". Similar to the first example, I then need to find the corresponding Date and Day value from the row that the highest value is located in and return those into Cell V7 & V8 respectively. On top of this I need to also find the corresponding column name from which the highest value came from to give the following information:

Cell V6:145
Cell V7: 28-Apr-09
Cell V8: Tuesday
Cell V9: Aisle 10

The table will eventually contain many more rows of data, so I cant work with just the range of information already in the table.

DUMMY SHEET 2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Date	Day	Load Type	Freezer	Dairy	Aisle 2	Aisle 3	Aisle 4	Aisle 5	Aisle 6	Aisle 7	Aisle 8	Aisle 9	Aisle 10	Aisle 11	Aisle 12	Aisle 13	Aisle 14	STORE
2	12-Feb-59	Wednesday	Double Sydney	33	48	62	77	43	67	61	82	64	70	97	58	50	76	72	60		MAXIMUM STORE VALUE (S)
3	26-Dec-62	Friday	Triple	29	46	75	81	47	68	71	83	39	65	82	51	44	63	68	58		Corresponding Date
4	21-May-83	Tuesday	No NDC	36	68	36	70	63	56	86	40	33	39	37	20	49	32	111	51		Corresponding Day
5	15-Oct-83	Friday	No NDC	35	63	44	110	44	66	85	44	48	43	45	33	65	66	131	58
6	09-Jan-86	Saturday	Double Sydney/Melbourne	28	42	76	76	48	51	53	66	64	56	101	47	54	62	87	57		MAXIMUM VALUE (D-R)
7	18-Jul-91	Thursday	Single Sydney	33	49	62	69	43	60	68	91	51	77	78	52	46	61	77	58		Correspoding Date
8	28-Nov-96	Thursday	Double Melbourne	35	58	44	67	41	57	75	80	57	67	87	53	44	51	59	58		Corresponding Day
9	28-Apr-09	Tuesday	Single Melbourne	23	34	66	84	41	48	94	67	57	56	145	53	45	64	60	52		Corresponding Column Name
10	11-Jun-13	Saturday	No NDC	35	63	44	73	44	66	85	44	48	43	30	33	65	66	131	56
11	10-Jan-19	Wednesday	Single Sydney	34	55	62	73	25	54	67	61	67	57	66	74	51	87	89	58
12
13
TEST CARTON RATE

Cell Formulas
Range	Formula
D2:D11	D2	=CartonCount4[@Freezer]/AisleHours8[@Freezer]
E2:E11	E2	=CartonCount4[@Dairy]/AisleHours8[@Dairy]
F2:F11	F2	=CartonCount4[@[Aisle 2]]/AisleHours8[@[Aisle 2]]
G2:G11	G2	=CartonCount4[@[Aisle 3]]/AisleHours8[@[Aisle 3]]
H2:H11	H2	=CartonCount4[@[Aisle 4]]/AisleHours8[@[Aisle 4]]
I2:I11	I2	=CartonCount4[@[Aisle 5]]/AisleHours8[@[Aisle 5]]
J2:J11	J2	=CartonCount4[@[Aisle 6]]/AisleHours8[@[Aisle 6]]
K2:K11	K2	=CartonCount4[@[Aisle 7]]/AisleHours8[@[Aisle 7]]
L2:L11	L2	=CartonCount4[@[Aisle 8]]/AisleHours8[@[Aisle 8]]
M2:M11	M2	=CartonCount4[@[Aisle 9]]/AisleHours8[@[Aisle 9]]
N2:N11	N2	=CartonCount4[@[Aisle 10]]/AisleHours8[@[Aisle 10]]
O2:O11	O2	=CartonCount4[@[Aisle 11]]/AisleHours8[@[Aisle 11]]
P2:P11	P2	=CartonCount4[@[Aisle 12]]/AisleHours8[@[Aisle 12]]
Q2:Q11	Q2	=CartonCount4[@[Aisle 13]]/AisleHours8[@[Aisle 13]]
R2:R11	R2	=CartonCount4[@[Aisle 14]]/AisleHours8[@[Aisle 14]]
S2:S11	S2	=CartonCount4[@TOTAL]/AisleHours8[@TOTAL]

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Date	Day	Load Type	Freezer	Dairy	Aisle 2	Aisle 3	Aisle 4	Aisle 5	Aisle 6	Aisle 7	Aisle 8	Aisle 9	Aisle 10	Aisle 11	Aisle 12	Aisle 13	Aisle 14	STORE
2	12-Feb-59	Wednesday	Double Sydney	33	48	62	77	43	67	61	82	64	70	97	58	50	76	72	60		MAXIMUM STORE VALUE (S)	60
3	26-Dec-62	Friday	Triple	29	46	75	81	47	68	71	83	39	65	82	51	44	63	68	58		Corresponding Date	18/07/1991
4	21-May-83	Tuesday	No NDC	36	68	36	70	63	56	86	40	33	39	37	20	49	32	111	51		Corresponding Day	Thursday
5	15-Oct-83	Friday	No NDC	35	63	44	110	44	66	85	44	48	43	45	33	65	66	131	58
6	09-Jan-86	Saturday	Double Sydney/Melbourne	28	42	76	76	48	51	145	66	64	56	101	47	54	62	87	57		MAXIMUM VALUE (D-R)	145
7	18-Jul-91	Thursday	Single Sydney	33	49	62	69	43	60	68	91	51	77	78	52	46	61	77	60		Correspoding Date	28/04/2009
8	28-Nov-96	Thursday	Double Melbourne	35	58	44	67	41	57	75	80	57	67	87	53	44	51	59	58		Corresponding Day	Tuesday
9	28-Apr-09	Tuesday	Single Melbourne	23	34	66	84	41	48	94	67	57	56	145	53	45	64	60	52		Corresponding Column Name	Aisle 10
10	11-Jun-13	Saturday	No NDC	35	63	44	73	44	66	85	44	48	43	30	33	65	66	131	56
11	10-Jan-19	Wednesday	Single Sydney	34	55	62	73	25	54	67	61	67	57	66	74	51	87	89	58
12
Master

Cell Formulas
Range	Formula
V2	V2	=MAX(Table1[STORE])
V3	V3	=AGGREGATE(14,6,Table1[Date]/(Table1[STORE]=V2),1)
V4,V8	V4	=TEXT(V3,"dddd")
V6	V6	=MAX(Table1[[Freezer]:[Aisle 14]])
V7	V7	=AGGREGATE(14,6,Table1[Date]/(Table1[[Freezer]:[Aisle 14]]=V6),1)
V9	V9	=INDEX(Table1[[#Headers],[Freezer]:[Aisle 14]],AGGREGATE(15,6,(COLUMN(Table1[[#Headers],[Freezer]:[Aisle 14]])-COLUMN(Table1[[#Headers],[Freezer]])+1)/(Table1[[Freezer]:[Aisle 14]]=V6)/(Table1[Date]=V7),1))

 

[Munch91]

Thanks again for your help.

I found the V4 & V8 formula didnt return the right day, it kept returning different ones. I worked around it by doing a VLOOKUP in there and referenced the results from Cells V3 & V6 respectively to find the associated day and that seems to work well when I test it across many dates.

 

[Fluff]

Glad to help & thanks for the feedback.