find row then use info in formula

[kasper]

Here's what I want to do.
I have a teacher schedule and I currently have a formula to count the number of times they cover a class : Teacher Name | =COUNTA($C$86:$Z$86)
This works great, but I have been manually entering the row data according to the teacher name. I would like this to work by itself since the data will be changing 4 times a year.
So, in table A I have Teacher Name followed by the number of classes covered, which is populated by table B which has each Teacher's name in column A followed by the date of each class covered in a row beside their name.

How do I change my working COUNTA formula to find and match the teacher in table A with their info in table B?

Thanks for your help!

 

[squidgeny]

What I would do is put =COUNTA($C$86:$Z$86) in AA86 (i.e., at the end of Table B)

And then use =VLOOKUP(A1,$C$1:$AA$86,25,False)

Adjust cell references as appropriate.

 

[Andrew Poulsom]

Teacher's name in A1 on Sheet1. Teachers' names in column A on Sheet2, dates worked in columns B onwards. In B1 on Sheet1:

=COUNTA(INDEX(Sheet2!B:IV,MATCH(A1,Sheet2!A:A,FALSE),))

 

[kasper]

Thanks squidgeny & Andrew!

I tried both options and found that Andrew's suggestion works best for my situation. However, if there is no Teacher's Name in the schedule, the formula returns the value 1. Why is this and how do I have the cell remain blank when there is no teacher value in the preceding cell?

Thanks.

 

[Andrew Poulsom]

Try:

=IF(LEN(A1),COUNTA(INDEX(Sheet2!B:IV,MATCH(A1,Sheet2!A:A,FALSE),)),"")

 

[kasper]

Almost there Andrew. That last formula did get rid of the value 1 in the blank teacher's cells, but is making all other values 1 more than it should be.

 

[Andrew Poulsom]

I can't reproduce that. If there is an entry in A1 the original formula is used.

 

[kasper]

SOLVED Re: find row then use info in formula

Thanks Andrew. I finally got it working with =IF(ISBLANK(A1),"",COUNTA(INDEX(Sheet2!B:IV,MATCH(A1,Sheet2!A:A,FALSE),)))
I appreciate your help!