Find result in table with multiple columns and return row header with formula

[Bengo]

Hi,

Trying to get the row header based on a cell value in a table with multiple columns

I am looking for the highest sales day over a period of time which is fine. But then I need to know which week that occured in

So for example in the image here, the max sales of 17,259 needs to return the date 07-May-17

I have tried this but this returns a #n/a error. Many thanks

=INDEX('workbook.xls]sheet1'!$A$3:$A$54,MATCH(MAX('[workbook.xls]sheet1'!$C$3:$I$54),'[wokbook.xls]sheet1'!$C$3:$I$54,0))

 

[Fluff]

Try

=INDEX([workbook.xls]sheet1!$A$3:$A$54,AGGREGATE(14,6,(ROW([workbook.xls]sheet1!$A$3:$A$54)-ROW([workbook.xls]sheet1!$A$3)+1)/([workbook.xls]sheet1!$C$3:$I$54=MAX([workbook.xls]sheet1!$C$3:$I$54)),1))

 

[Toadstool]

Hi Bengo,

MATCH won't work on a matrix, only cells in a single row or column.

I see @Fluff has beaten me again to posting a solution so I must go a step further and handle duplicate maximum sales values (here 7 May 2017 and 17 June 2017) and provide a list of the "Top 10" sales weeks.

Bengo.xlsx
	A	B	C	D	E	F	G	H	I
1
2		W/E	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
3		09-Apr-17	7283	5373	8250	2773	15133	5961	4492
4		16-Apr-17	8002	10736	14671	4291	6081	9472	5760
5		23-Apr-17	6206	8714	8352	3024	7801	13993	2515
6		30-Apr-17	10755	6196	16282	10077	9969	7766	3616
7		07-May-17	4236	5356	5193	8934	12949	17259	15130
8		14-May-17	11374	14589	6743	9882	7355	15113	7558
9		21-May-17	7740	8259	4590	3370	6252	3735	14603
10		28-May-17	16122	4946	14564	5892	4705	14997	11794
11		04-Jun-17	2536	14346	13358	9061	13757	6015	12806
12		11-Jun-17	3158	2537	10777	4372	14247	14515	10124
13		18-Jun-17	14473	13329	2872	10561	5162	9991	10839
14		25-Jun-17	3551	15130	15130	13650	11920	17259	14252
15		02-Jul-17	4717	11214	6891	13797	5546	7153	4833
Sheet1

Cell Formulas
Range	Formula
B4:B15	B4	=B3+7

Bengo.xlsx
	A	B	C
1	Position	Sales	W/E
2	1	17,259	07-May-17
3	1	17,259	25-Jun-17
4	3	16,282	30-Apr-17
5	4	16,122	28-May-17
6	5	15,133	09-Apr-17
7	6	15,130	07-May-17
8	6	15,130	25-Jun-17
9	6	15,130	25-Jun-17
10	9	15,113	14-May-17
11	10	14,997	28-May-17
Sheet2

Cell Formulas
Range	Formula
A2:A11	A2	=COUNTIF($B$2:$B$11,">"&B2)+1
B2:B11	B2	=AGGREGATE(14,6,Sheet1!$C$3:$I$9999,ROW()-ROW($B$1))
C2:C11	C2	=INDEX(Sheet1!$B$3:$B$9999,AGGREGATE(15,6,ROW(Sheet1!$B$3:$B$9999)-ROW(Sheet1!$B$2)/((Sheet1!$C$3:$I$9999=B2)),COUNTIF($B$1:$B2,B2)))

 

[Bengo]

Try

=INDEX([workbook.xls]sheet1!$A$3:$A$54,AGGREGATE(14,6,(ROW([workbook.xls]sheet1!$A$3:$A$54)-ROW([workbook.xls]sheet1!$A$3)+1)/([workbook.xls]sheet1!$C$3:$I$54=MAX([workbook.xls]sheet1!$C$3:$I$54)),1))

Thanks so much this has worked perfectly!! I need to go away and figure out what that is doing, but for now I have created the model I am after!! Ver much appreciated!

 

[Bengo]

Hi Bengo,

MATCH won't work on a matrix, only cells in a single row or column.

I see @Fluff has beaten me again to posting a solution so I must go a step further and handle duplicate maximum sales values (here 7 May 2017 and 17 June 2017) and provide a list of the "Top 10" sales weeks.

Bengo.xlsx
	A	B	C	D	E	F	G	H	I
1
2		W/E	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
3		09-Apr-17	7283	5373	8250	2773	15133	5961	4492
4		16-Apr-17	8002	10736	14671	4291	6081	9472	5760
5		23-Apr-17	6206	8714	8352	3024	7801	13993	2515
6		30-Apr-17	10755	6196	16282	10077	9969	7766	3616
7		07-May-17	4236	5356	5193	8934	12949	17259	15130
8		14-May-17	11374	14589	6743	9882	7355	15113	7558
9		21-May-17	7740	8259	4590	3370	6252	3735	14603
10		28-May-17	16122	4946	14564	5892	4705	14997	11794
11		04-Jun-17	2536	14346	13358	9061	13757	6015	12806
12		11-Jun-17	3158	2537	10777	4372	14247	14515	10124
13		18-Jun-17	14473	13329	2872	10561	5162	9991	10839
14		25-Jun-17	3551	15130	15130	13650	11920	17259	14252
15		02-Jul-17	4717	11214	6891	13797	5546	7153	4833
Sheet1

Cell Formulas
Range	Formula
B4:B15	B4	=B3+7

Bengo.xlsx
	A	B	C
1	Position	Sales	W/E
2	1	17,259	07-May-17
3	1	17,259	25-Jun-17
4	3	16,282	30-Apr-17
5	4	16,122	28-May-17
6	5	15,133	09-Apr-17
7	6	15,130	07-May-17
8	6	15,130	25-Jun-17
9	6	15,130	25-Jun-17
10	9	15,113	14-May-17
11	10	14,997	28-May-17
Sheet2

Cell Formulas
Range	Formula
A2:A11	A2	=COUNTIF($B$2:$B$11,">"&B2)+1
B2:B11	B2	=AGGREGATE(14,6,Sheet1!$C$3:$I$9999,ROW()-ROW($B$1))
C2:C11	C2	=INDEX(Sheet1!$B$3:$B$9999,AGGREGATE(15,6,ROW(Sheet1!$B$3:$B$9999)-ROW(Sheet1!$B$2)/((Sheet1!$C$3:$I$9999=B2)),COUNTIF($B$1:$B2,B2)))

Thanks very much for this, helps explain the above much more easily, will have a good read through! thanks again

 

[Fluff]

Glad we could help & thanks for the feedback.