I'm trying to achieve the following in Excel: I have two worksheets with details of students and their marks for three different subjects.

Worksheet 1 has a Student Name column with names of all students in the class and a Subject column that tells which subject they have scored the least in.

Worksheet 2 has the same Student Name column but the rows may not be in the same order. Worksheet 2 has three more columns named English, Maths and Science populated with the marks scored by the students.

The Subject column for each student in Worksheet 1 should look up the Student Name column in Worksheet 2 for the corresponding student name and then from that row identify the lowest value and return the subject name (which is the first value in the column/table header of the lowest value). I was thinking of using the VLOOKUP function but I'm not sure how I can nest another function within it to search for the lowest value in the row and return the subject name/table header value.

CodePudding user response：

You can use this formula:

• Formula used in cell B2

=LET(_marks,INDEX($E$2:$G$10,MATCH($A2,$D$2:$D$10,0),0),
FILTER($E$1:$G$1,MIN(_marks)=_marks))

Edit:

When a student scores same minimum marks for two subjects. Then we can use as below to return both the subjects,

• Formula used in cell B2

=LET(_marks,INDEX($E$2:$G$10,MATCH($A2,$D$2:$D$10,0),0),
ARRAYTOTEXT(FILTER($E$1:$G$1,MIN(_marks)=_marks)))