Find a value in multiple row and return the respective rows 1st cell value

[sathishkumarc]

I have a table that has the list of Teacher names in the first column and the subjects that they handle in the following columns. (as shows below), A Teacher can handle multiple subjects, so the columns can be more than 3.
A B C D
Teacher1 Eng1 Eng2 Science1
Teacher2 Maths2 Eng3 Lifeskills1

I have another table which has the list of all the subjects. (As shows below)
A B
Eng1
Eng2
Eng3
Maths2
Lifeskills1
Science1

I want a formula in the column B in this table that would search the previous table and return the teacher name. Ex: for B1 the result must be "Teacher 1"

Please assist

 

[Rick Rothstein]

I have a table that has the list of Teacher names in the first column and the subjects that they handle in the following columns. (as shows below), A Teacher can handle multiple subjects, so the columns can be more than 3.
A B C D
Teacher1 Eng1 Eng2 Science1
Teacher2 Maths2 Eng3 Lifeskills1

I have another table which has the list of all the subjects. (As shows below)
A B
Eng1
Eng2
Eng3
Maths2
Lifeskills1
Science1

I want a formula in the column B in this table that would search the previous table and return the teacher name. Ex: for B1 the result must be "Teacher 1"

Assuming the first table is on Sheet1, put this array-entered** formula in B1 on the second table and copy it down to the end of the list...

=INDEX(Sheet1!A$1:A$2,MAX(IF(Sheet1!B$1:D$2=A1,ROW(Sheet1!B$1:D$2),0)))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

NOTE: The red number 2's need to be changed to the row number of the last teacher name on Sheet1 and the green letter D's need to be changed to the letter designation for the last course column on Sheet1.

 

[sathishkumarc]

Thanks for the reply Rick.
I USED {=INDEX($A$2:$A$11,MAX(IF($B$2:$M$11=A32,ROW($B$2:$M$11),0)))}
This formula is returning incorrect teacher name! Not sure what's wrong. let me provide more data so you could helm me fix this.
I have both the table in the same sheet. (i HAVE MODIFIED THE FORMULA YOU PROVIDED AND USED THE LOGIC) and this is the range and data.


TABLE 1 (DATA RANGE A2:M11)
A B C D E F G H I J K L M
TEACHER NAME GRADE SUBJECT GRADE SUBJECT GRADE SUBJECT GRADE SUBJECT GRADE SUBJECT GRADE SUBJECT
[TABLE="width: 854"]
<colgroup><col><col><col><col><col span="5"></colgroup><tbody>[TR]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1245"]
<colgroup><col><col><col><col span="10"></colgroup><tbody>[TR]
[TD]Kavitha
[/TD]
[TD]Grade 5[/TD]
[TD]English 5
[/TD]
[TD]Grade 6[/TD]
[TD]SoSc 6[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[/TR]
[TR]
[TD]Pramela[/TD]
[TD]Grade 1[/TD]
[TD]Hindi 1[/TD]
[TD]Grade 2[/TD]
[TD]Hindi 2[/TD]
[TD]Grade 3[/TD]
[TD]Hindi 3[/TD]
[TD]Grade 4[/TD]
[TD]Hindi 4[/TD]
[TD]Grade 5[/TD]
[TD]Hindi 5[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[/TR]
[TR]
[TD]Thillaikarasi[/TD]
[TD]Grade 1[/TD]
[TD]English 1[/TD]
[TD]Grade 2[/TD]
[TD]English 2[/TD]
[TD]Grade 3[/TD]
[TD]English 3[/TD]
[TD]Grade 3[/TD]
[TD]Maths 3[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[/TR]
[TR]
[TD]Priyanka[/TD]
[TD]Grade 4[/TD]
[TD]Maths 4[/TD]
[TD]Grade 4[/TD]
[TD]EVS 4[/TD]
[TD]Grade 5[/TD]
[TD]Maths 5[/TD]
[TD]Grade 5
[/TD]
[TD]EVS 5[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[/TR]
[TR]
[TD]Soniya[/TD]
[TD]Grade 1[/TD]
[TD]EVS 1[/TD]
[TD]Grade 2[/TD]
[TD]EVS 2[/TD]
[TD]Grade 3[/TD]
[TD]EVS 3[/TD]
[TD]Grade 3[/TD]
[TD]Tamil 3[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[/TR]
[TR]
[TD]Umamaheshwari[/TD]
[TD]Grade 4[/TD]
[TD]English 4[/TD]
[TD]Grade 6[/TD]
[TD]English 6[/TD]
[TD]Grade 6[/TD]
[TD]EVS 6[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[/TR]
[TR]
[TD]Nithya[/TD]
[TD]Grade 1[/TD]
[TD]Maths 1[/TD]
[TD]Grade 1[/TD]
[TD]Tamil 1[/TD]
[TD]Grade 2[/TD]
[TD]Maths 2[/TD]
[TD]Grade 2[/TD]
[TD]Tamil 2[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[/TR]
[TR]
[TD]Sakthivel[/TD]
[TD]Grade 4[/TD]
[TD]Tamil 4[/TD]
[TD]Grade 5[/TD]
[TD]Tamil 5[/TD]
[TD]Grade 6[/TD]
[TD]Tamil 6[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[/TR]
[TR]
[TD]Sasikala[/TD]
[TD]Grade 1[/TD]
[TD]CS 1[/TD]
[TD]Grade 2[/TD]
[TD]CS 2[/TD]
[TD]Grade 3[/TD]
[TD]CS 3[/TD]
[TD]Grade 4[/TD]
[TD]CS 4[/TD]
[TD]Grade 5[/TD]
[TD]CS 5[/TD]
[TD]Grade 6[/TD]
[TD]CS 6[/TD]
[/TR]
[TR]
[TD]Karthik[/TD]
[TD]Grade 1[/TD]
[TD]ESL 1[/TD]
[TD]Grade 2[/TD]
[TD]ESL 2[/TD]
[TD]Grade 3[/TD]
[TD]ESL 3[/TD]
[TD]Grade 4[/TD]
[TD]ESL 4[/TD]
[TD]Grade 5[/TD]
[TD]ESL 5[/TD]
[TD]Grade 6[/TD]
[TD]ESL 6[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Grade 1[/TD]
[TD]PET 1[/TD]
[TD]Grade 2[/TD]
[TD]PET 2[/TD]
[TD]Grade 3[/TD]
[TD]PET 3[/TD]
[TD]Grade 4[/TD]
[TD]PET 4[/TD]
[TD]Grade 5[/TD]
[TD]PET 5[/TD]
[TD]Grade 6[/TD]
[TD]PET 6
[/TD]
[/TR]
</tbody>[/TABLE]

TABLE 2 (DATA RANGE A32:L41) THE FORMULA HAS TO BE ENTERED IN COLUMN B,D,F,H,J & L SHOWN IN BLUE BELOW TO RETRIEVE THE RESPECTIVE TEACHERS NAME FROM TABLE 1.
A B C D E F G H I J K L

[TABLE="width: 1080"]
<colgroup><col><col><col span="10"></colgroup><tbody>[TR]
[TD]
English 1
[/TD]
[TD][/TD]
[TD]English 2[/TD]
[TD] [/TD]
[TD]English 3[/TD]
[TD] [/TD]
[TD]English 4[/TD]
[TD] [/TD]
[TD]English 5[/TD]
[TD] [/TD]
[TD]English 6[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Tamil 1[/TD]
[TD][/TD]
[TD]Tamil 2[/TD]
[TD] [/TD]
[TD]Tamil 3[/TD]
[TD] [/TD]
[TD]Tamil 4[/TD]
[TD] [/TD]
[TD]Tamil 5[/TD]
[TD] [/TD]
[TD]Tamil 6[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Hindi 1[/TD]
[TD][/TD]
[TD]Hindi 2[/TD]
[TD] [/TD]
[TD]Hindi 3[/TD]
[TD] [/TD]
[TD]Hindi 4[/TD]
[TD] [/TD]
[TD]Hindi 5[/TD]
[TD] [/TD]
[TD]Maths 6[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Maths 1[/TD]
[TD][/TD]
[TD]Maths 2[/TD]
[TD] [/TD]
[TD]Maths 3[/TD]
[TD] [/TD]
[TD]Maths 4[/TD]
[TD] [/TD]
[TD]Maths 5[/TD]
[TD] [/TD]
[TD]EVS 6[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]EVS 1[/TD]
[TD][/TD]
[TD]EVS 2[/TD]
[TD] [/TD]
[TD]EVS 3[/TD]
[TD] [/TD]
[TD]EVS 4[/TD]
[TD] [/TD]
[TD]EVS 5[/TD]
[TD] [/TD]
[TD]SoSc 6[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CS 1[/TD]
[TD][/TD]
[TD]CS 2[/TD]
[TD] [/TD]
[TD]CS 3[/TD]
[TD] [/TD]
[TD]CS 4[/TD]
[TD] [/TD]
[TD]CS 5[/TD]
[TD] [/TD]
[TD]CS 6[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ESL 1[/TD]
[TD][/TD]
[TD]ESL 2[/TD]
[TD] [/TD]
[TD]ESL 3[/TD]
[TD] [/TD]
[TD]ESL 4[/TD]
[TD] [/TD]
[TD]ESL 5[/TD]
[TD] [/TD]
[TD]ESL 6[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]LS 1[/TD]
[TD][/TD]
[TD]LS 2[/TD]
[TD] [/TD]
[TD]LS 3[/TD]
[TD] [/TD]
[TD]LS 4[/TD]
[TD] [/TD]
[TD]LS 5[/TD]
[TD] [/TD]
[TD]LS 6[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]PET 1[/TD]
[TD][/TD]
[TD]PET 2[/TD]
[TD] [/TD]
[TD]PET 3[/TD]
[TD] [/TD]
[TD]PET 4[/TD]
[TD] [/TD]
[TD]PET 5[/TD]
[TD] [/TD]
[TD]PET 6[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Library 1[/TD]
[TD][/TD]
[TD]Library 2[/TD]
[TD] [/TD]
[TD]Library 3[/TD]
[TD] [/TD]
[TD]Library 4[/TD]
[TD] [/TD]
[TD]Library 5[/TD]
[TD] [/TD]
[TD]Library 6[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]


P.S not all subject is allocated to a teacher, few are unallocated. that must return null.
Plz let me know if you need more info

 

[bosco_yip]

I USED {=INDEX($A$2:$A$11,MAX(IF($B$2:$M$11=A32,ROW($B$2:$M$11),0)))}

Try :

{=INDEX($A$2:$A$11,MAX(IF($B$2:$M$11=A32,ROW($B$2:$M$11)-1,0)))}

Regards

 

[XOR LX]

{=INDEX($A$2:$A$11,MAX(IF($B$2:$M$11=A32,ROW($B$2:$M$11)-1,0)))}

Rather than the addition/subtraction of fixed constants to this expression (which will in any case need re-determining by the OP should they one day decide to move their range elsewhere in the sheet), it's much better to make this part dynamic, so that it is independent of the range being queried.

Either:

=INDEX($A$2:$A$11,MAX(IF($B$2:$M$11=A32,ROW($B$2:$M$11)-MIN(ROW($B$2:$M$11))+1)))

or:

=INDEX($A$2:$A$11,MAX(IF($B$2:$M$11=A32,ROW($B$2:$M$11)-ROW($B$2)+1)))

is preferable.

Regards

 

[bosco_yip]

...... not all subject is allocated to a teacher, few are unallocated. that must return null.
Plz let me know if you need more info

Then the formula adjusted to :

=IF(COUNTIF($B$2:$M$11,A32)>0,INDEX($A$2:$A$11,MAX(IF($B$2:$M$11=A32,ROW($B$2:$M$11)-1,0))),"")

Regards

 

[sathishkumarc]

=INDEX($A$2:$A$11,MAX(IF($B$2:$M$11=A32,ROW($B$2:$M$11)-ROW($B$2)+1)))

cool, this works! Thanks a lot!