+ Reply to Thread
Results 1 to 13 of 13

Excel 2007 : Formula does not work

  1. #1
    Registered User
    Join Date
    08-29-2009
    Location
    Boston
    MS-Off Ver
    Excel 2007
    Posts
    7

    Formula does not work

    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!
    Last edited by NBVC; 09-01-2009 at 09:30 PM.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Formula does not work

    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?

  3. #3
    Registered User
    Join Date
    08-29-2009
    Location
    Boston
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Formula does not work

    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.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Formula does not work

    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),"")
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  5. #5
    Registered User
    Join Date
    08-29-2009
    Location
    Boston
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Formula does not work

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

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Formula does not work

    =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

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Formula does not work

    Given use of XL2007 you could possibly also use:

    Please Login or Register  to view this content.
    but the IFERROR is a "catch-all" so could mask other unexpected errors.

  8. #8
    Registered User
    Join Date
    08-29-2009
    Location
    Boston
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Formula does not work

    Hello,

    Sorry, I went to bed last night.

    Quote Originally Posted by NBVC View Post
    =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.

  9. #9
    Registered User
    Join Date
    08-29-2009
    Location
    Boston
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Formula does not work

    I will try that as well Donkey0te

  10. #10
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Formula does not work

    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)),"")

  11. #11
    Registered User
    Join Date
    08-29-2009
    Location
    Boston
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Formula does not work

    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...)

  12. #12
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Formula does not work

    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"

  13. #13
    Registered User
    Join Date
    08-29-2009
    Location
    Boston
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Formula does not work

    The firs formula works!

    Thanks you very much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1