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

S

sathishkumarc

New Member
Joined
Feb 13, 2015
Messages
4
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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
sathishkumarc said:
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"
Click to expand...
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.
 
Upvote 0
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! :confused: 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

<colgroup><col><col><col><col><col span="5"></colgroup><tbody>
</tbody>
Kavitha
Grade 5English 5
Grade 6SoSc 6FREEFREEFREEFREEFREEFREEFREEFREE
PramelaGrade 1Hindi 1Grade 2Hindi 2Grade 3Hindi 3Grade 4Hindi 4Grade 5Hindi 5FREEFREE
ThillaikarasiGrade 1English 1Grade 2English 2Grade 3English 3Grade 3Maths 3FREEFREEFREEFREE
PriyankaGrade 4Maths 4Grade 4EVS 4Grade 5Maths 5Grade 5
EVS 5FREEFREEFREEFREE
SoniyaGrade 1EVS 1Grade 2EVS 2Grade 3EVS 3Grade 3Tamil 3FREEFREEFREEFREE
UmamaheshwariGrade 4English 4Grade 6English 6Grade 6EVS 6FREEFREEFREEFREEFREEFREE
NithyaGrade 1Maths 1Grade 1Tamil 1Grade 2Maths 2Grade 2Tamil 2FREEFREEFREEFREE
SakthivelGrade 4Tamil 4Grade 5Tamil 5Grade 6Tamil 6FREEFREEFREEFREEFREEFREE
SasikalaGrade 1CS 1Grade 2CS 2Grade 3CS 3Grade 4CS 4Grade 5CS 5Grade 6CS 6
KarthikGrade 1ESL 1Grade 2ESL 2Grade 3ESL 3Grade 4ESL 4Grade 5ESL 5Grade 6ESL 6
Grade 1PET 1Grade 2PET 2Grade 3PET 3Grade 4PET 4Grade 5PET 5Grade 6PET 6

<colgroup><col><col><col><col span="10"></colgroup><tbody>
</tbody>

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


English 1
English 2 English 3 English 4 English 5 English 6
Tamil 1Tamil 2 Tamil 3 Tamil 4 Tamil 5 Tamil 6
Hindi 1Hindi 2 Hindi 3 Hindi 4 Hindi 5 Maths 6
Maths 1Maths 2 Maths 3 Maths 4 Maths 5 EVS 6
EVS 1EVS 2 EVS 3 EVS 4 EVS 5 SoSc 6
CS 1CS 2 CS 3 CS 4 CS 5 CS 6
ESL 1ESL 2 ESL 3 ESL 4 ESL 5 ESL 6
LS 1LS 2 LS 3 LS 4 LS 5 LS 6
PET 1PET 2 PET 3 PET 4 PET 5 PET 6
Library 1Library 2 Library 3 Library 4 Library 5 Library 6

<colgroup><col><col><col span="10"></colgroup><tbody>
</tbody>


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
 
Upvote 0
bosco_yip said:
{=INDEX($A$2:$A$11,MAX(IF($B$2:$M$11=A32,ROW($B$2:$M$11)-1,0)))}
Click to expand...

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
 
Upvote 0
sathishkumarc said:
...... not all subject is allocated to a teacher, few are unallocated. that must return null.
Plz let me know if you need more info
Click to expand...

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
 
Upvote 0
=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!:)
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Return multiple deliminated values from lookup
Replies
2
Views
134
jimbomcmucka
J
N
  • Question
Search for multiple values within a single cell
Replies
6
Views
215
Peter_SSs
Peter_SSs
S
  • Solved
Find Text in a cell
Replies
5
Views
176
Fluff
Fluff
I
  • Question
Find duplicate column values across multiple workbooks, and get data row and set row data to new sheet
Replies
2
Views
209
itomedina25
I
D
  • Question
Find matching value in table, check if specific cell in same row is empty, find next if not
Replies
0
Views
248
dubyarly
D

Forum statistics

Threads
1,215,974
Messages
6,128,045
Members
449,416
Latest member
SHIVANISHARMA1711

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top