Search for data in a matrix from several parameters.

[quemuenchatocha]

Dear all, have a nice day.

I appreciate any information you can give me about the following question.

I am performing a search process that allows me to find, within a set of students [A2:A9], who obtained the highest grade [B2:I9], for a set of subjects [B1:I1]. I found a solution, but it requires to know first the name of the student [H11] or very well the name of the subject [K11].

Student data.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Name	Lab 1	Lab 2	Lab 3	HW 1	HW 2	HW 3	Exam 1	Exam 2
2	Jim	9	4	8,5	23	24	22	90	87
3	Henry	8	7,5	10	20	20	24	87	94,5
4	Sally	10	8,5	9,5	21	23,5	19,5	78	84
5	Pinocchio	7,5	10	7,5	25	19	21	95	91,5
6	Heather	8	9	10	23	20,5	23	93	96
7	Ella	9,5	10	10	22	19	20,5	100	99
8	Tom	8,5	6,5	8,5	24	24,5	23,5	87,5	98,5
9	Nancy	10	8	9	20,5	23,5	24	82	95
10
11		Maximum:	100				Student	Ella		Signature	Exam 1
12		Who?:	Ella				Max Score	100		Max Score	100
13		Assignment:	Exam 1				Signature	Exam 1		Student	Ella
14
Grades

Cell Formulas
Range	Formula
C11	C11	=MAX(B2:I9)
C12	C12	=INDEX(A2:A9,MATCH(C11,OFFSET(A1,1,7,8,1),0))
C13	C13	=INDEX(B1:I1,MATCH(C11,OFFSET(B1,MATCH(C12,A2:A9,0),0,1,8)))
H12	H12	=MAX(OFFSET(A1,MATCH(H11,$A$2:$A$9,0),1,1,8))
H13	H13	=INDEX(B1:I1,MATCH(H12,OFFSET(A1,MATCH(H11,A2:A9,0),1,1,8),0))
K12	K12	=MAX(OFFSET(A1,1,MATCH(K11,B1:I1,0),8,1))
K13	K13	=INDEX(A2:A9,MATCH(K12,OFFSET(A1,1,MATCH(K11,B1:I1,0),8,1),0))

Cells with Data Validation
Cell	Allow	Criteria
H11	List	=$A$2:$A$9
K11	List	=$B$1:$I$1

My question is focused then on knowing if it is possible only from the maximum score [C11], to know which was the student (name) who obtained the maximum grade [C12]?

I tried the following formula C12=INDEX(A2:A9,MATCH(C11,OFFSET(A1,1,7,8,1),0)), but it is clear that the values entered in the OFFSET function for Row and Column are previously known, so it is not the procedure I am looking for.

Thank you very much for your attention.

 

[Fluff]

How about in C12

=INDEX(A2:A9,AGGREGATE(15,6,(ROW(A2:A9)-ROW(A2)+1)/(B2:I9=C11),1))

and C13

=INDEX(B1:I1,AGGREGATE(15,6,(COLUMN(B1:I1)-COLUMN(B1)+1)/(A2:A9=C12)/(B2:I9=C11),1))

 

[alansidman]

An alternative is to use Power Query
Excel 2016 (Windows) 32 bit

	A​	B​	C​
12​	Attribute	Max Grade	Table1 (2).Name
13​	Exam 1	100​	Ella
14​	Exam 2	99​	Ella
15​	HW 1	25​	Pinocchio
16​	HW 2	24.5​	Tom
17​	HW 3	24​	Henry
18​	HW 3	24​	Nancy
19​	Lab 1	10​	Nancy
20​	Lab 1	10​	Sally
21​	Lab 2	10​	Pinocchio
22​	Lab 2	10​	Ella
23​	Lab 3	10​	Henry
24​	Lab 3	10​	Ella
25​	Lab 3	10​	Heather

Sheet: Sheet1

Unpivot your data in Power Query and then join the unpivoted data back against the original table.

Here are the Mcodes if you wish to go down that road

Unpivot original table and group to find Max score

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Lab 1", type number}, {"Lab 2", type number}, {"Lab 3", type number}, {"HW 1", type number}, {"HW 2", type number}, {"HW 3", type number}, {"Exam 1", type number}, {"Exam 2", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Attribute"}, {{"Max Grade", each List.Max([Value]), type number}})
in
    #"Grouped Rows"

Take original table and unpivot only with a new name.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

Now join the two tables to find who had the high scores.

let
    Source = Table.NestedJoin(Table1, {"Attribute", "Max Grade"}, #"Table1 (2)", {"Attribute", "Value"}, "Table1 (2)", JoinKind.LeftOuter),
    #"Expanded Table1 (2)" = Table.ExpandTableColumn(Source, "Table1 (2)", {"Name"}, {"Table1 (2).Name"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Table1 (2)",{{"Attribute", Order.Ascending}})
in
    #"Sorted Rows"

 

[quemuenchatocha]

How about in C12

=INDEX(A2:A9,AGGREGATE(15,6,(ROW(A2:A9)-ROW(A2)+1)/(B2:I9=C11),1))

and C13

=INDEX(B1:I1,AGGREGATE(15,6,(COLUMN(B1:I1)-COLUMN(B1)+1)/(A2:A9=C12)/(B2:I9=C11),1))

Dear @Fluff , thank you very much for your valuable collaboration, it has been useful to solve my doubt.

 

[quemuenchatocha]

Dear @alansidman you are very kind to teach me this solution, personally I have little knowledge about Power Query, it is something I have pending to learn, I would like if possible, give me some tips (blogs, tutorials,...) to start with training in this valuable tool, thanks for your collaboration.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[alansidman]

For starters, there is a book in the MrE store by Ken Puls and Miguel Escobar called Master Your Data with Power Query in Excel and Power BI
This is a great book to help you learn Power Query. It is easy to follow. Click here--> Products

 

[quemuenchatocha]

For starters, there is a book in the MrE store by Ken Puls and Miguel Escobar called Master Your Data with Power Query in Excel and Power BI
This is a great book to help you learn Power Query. It is easy to follow. Click here--> Products

Thank you very much for your valuable guidance, I will review the recommended material, a big hug!