Excel 2007 : Formula does not work

[hot_ice1027]

Hello,

Could anyone tell me why this formula does not work?
I mean it works, but returns wrong rows.

=IF(ISERROR(INDEX('Student List'!$B$1:$B$100,AA2)),"",INDEX('Student List'!$B$1:$B$100,AA2))


Thanks!

[daddylonglegs]

What do you expect it to do?

Presumably AA2 contains a number. If that number is n then the formula should return the value from the nth cell of range B1:B100 in worksheet Student List, e.g. if AA2 =50 then formula returns the value from B50, is that what you want?

[hot_ice1027]

For simplicity's sake, I have two sheets. One of them "Student List" and the other "Test".
In "Student List" I have around 70 columns. One of which is Last Name of a student, and the other one is a Teacher's Initials. Naturally, one teacher can have multiple students.
Also there could be duplicate last names in one class.
So, the idea is as follows: In the Test sheet I want to type in the teacher's initials (just like that - AC) in some cell, and the cells next to it will be populated with the student last names that are in AC class.
Does that make sense?
Thanks.

[NBVC]

Is AA2 in your formula the cell containing the teacher's initials?

If yes, then the formula should be more like this:

=IF(ISNUMBER(MATCH(AA2,'Student List',$A$1:$A$100,0)),INDEX('Student List'!$B$1:$B$100,MATCH(AA2,'Student List',$A$1:$A$100,0),"")

where Student List!A1:A100 contain teacher's initials and B1:B100 corresponding students.

or:

=IF(ISNUMBER(MATCH(AA2,'Student List',$A$1:$A$100,0)), VLOOKUP(AA2,'Student List'!$A$1:$B$100,2,0),"")

[hot_ice1027]

"Student List" sheet:
Column B (range B2:B673) - Last Name
Column AE (range AE2:AE673) - Teacher's Initials.

[NBVC]

=IF(ISNUMBER(MATCH(AA2,'Student List',AE$2:AE$673,0)),INDEX('Student List'!B$2:B$6730,MATCH(AA2,'Student List',AE$2:AE$673,0),"")

Assuming AA2 is where the teacher initials to match is on your current sheet.. change if necessary and copy down formula

[DonkeyOte]

Given use of XL2007 you could possibly also use:

=IFERROR(INDEX('Student List'!B$2:B$6730,MATCH(AA2,'Student List',AE$2:AE$673,0),"")
note: not backwards compatible with earlier versions

but the IFERROR is a "catch-all" so could mask other unexpected errors.

[hot_ice1027]

I will try that as well Donkey0te

[daddylonglegs]

looks like a few typos creeping in there......, NBVC's should be

=IF(ISNUMBER(MATCH(AA2,'Student List'!AE$2:AE$673,0)),INDEX('Student List'!B$2:B$673,MATCH(AA2,'Student List'!AE$2:AE$673,0)),"")

and Donkeyote's....in Excel 2007 only

=IFERROR(INDEX('Student List'!B$2:B$673,MATCH(AA2,'Student List'!AE$2:AE$673,0)),"")

[hot_ice1027]

Hello,

Sorry, I went to bed last night.

=IF(ISNUMBER(MATCH(AA2,'Student List',AE$2:AE$673,0)),INDEX('Student List'!B$2:B$6730,MATCH(AA2,'Student List',AE$2:AE$673,0),"")

Assuming AA2 is where the teacher initials to match is on your current sheet.. change if necessary and copy down formula

In the INDEX part of formula, did you mean to have a comma between B$673 and a 0? ...,INDEX('Student List'!B$2:B$673,0,MATCH...

Thanks.

[hot_ice1027]

OK, thanks for the corrections.
However, none of those formulas return anything.
And I know that in "Student List" there is teacher initial "AC" that in the
AE409:AE419 range.

So once again, is it possible if I type "AC" in cell A2 in "Test" sheet, and the range B2:B15 (assuming 14 rows are returned) are populated with last names from "Student List" which are in B2:B673 range?

Thanks again. (It just bugs me how much time I have spent on this...)

[daddylonglegs]

You mention cell A2 with teacher's initials but the corrected formulas I posted assume initials in AA2.....

In any case I didn't realise you wanted multiple matches....if you have teacher initials in A2 try this formula in B2

=IFERROR(INDEX('Student List'!B$2:B$673,SMALL(IF(A$2='Student List'!AE$2:AE$673,ROW('Student List'!B$2:B$673)-ROW('Student List'!B$2)+1),ROWS(B$2:B2))),"")

This is an "array formula" which needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces appear around the formula in the formula bar

copy down column from B2 as far as required, when names are exhausted blanks will be returned.....

That formula is designed to extract the names with the data in any order. It doesn't check for duplicate names, if the names are duplicated it will just extract them twice, I'm not clear if that's what you need.....

If each teacher is shown as a block with students in successive rows then you can extract them more efficiently with this formula in B2

=IF(ROWS(B$2:B2)>COUNTIF('Student List'!AE$2:AE$673,A$2),"",INDEX('Student List'!B$2:B$673,MATCH(A$2,'Student List'!AE$2:AE$673,0)+ROWS(B$2:B2)-1))

which doesn't need to be "array-entered"

[hot_ice1027]

The firs formula works!

Thanks you very much!