+ Reply to Thread
Results 1 to 8 of 8

Vlookup formula not working

  1. #1
    Registered User
    Join Date
    11-23-2010
    Location
    Wenatchee, WA
    MS-Off Ver
    Excel 2004
    Posts
    4

    Vlookup formula not working

    Noob...be gentle

    I need help with a formula. I've used vlookup in the past but I don't think it will get the job done on this one.

    Situation: I have two sheets in my workbook. One Sheet1 I have a column (column A) with 100 student's names in it. I also have a column (column B) with Teacher's names in it.

    On Sheet2 (an individual teacher's page) I want to be able to write a formula that will look on the top of the sheet (in cell A1) to find the teacher's name, then reference every instance of that name on Sheet1 (column B) and return all of the student's names from column A).

    If someone has a formula or an example spreadsheet to pass along I would greatly appreciate it.

    Ray
    Last edited by rbwen; 12-02-2011 at 02:12 PM.

  2. #2
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Vlookup formula not working

    Welcome to the forum. There's not a really simple formula to do what you want to do, but you can see if this works: put
    Please Login or Register  to view this content.
    in B1 and drag down as needed. In C1, put
    Please Login or Register  to view this content.
    and drag down and over as needed.
    Life is about perseverance. Remember: today's mighty oak tree is just yesterday's nut that held its ground.

    If you like a post, please rate it with the scales icon (top right).
    If you feel really indebted, please consider a donation to charity, such as Feed the Children or Habitat for Humanity.

  3. #3
    Registered User
    Join Date
    11-23-2010
    Location
    Wenatchee, WA
    MS-Off Ver
    Excel 2004
    Posts
    4

    Re: Vlookup formula not working

    Thanks for the formulas. It seems to work well for the first two returns and then it gets weird. I've attached a sample file so you can see what it's doing. The D column on Sheet2 is what's strange.

    Also, how nicely do these formulas play with Google Spreadsheets?
    Attached Files Attached Files

  4. #4
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Vlookup formula not working

    I made two minor errors. I didn't put in a ,0 in one of the match statements (telling it to look up an exact match), and I didn't freeze one of the references to a column.

    I also think we had a different layout in mind. This is more what I had been envisioning, just one line for each teacher.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-23-2010
    Location
    Wenatchee, WA
    MS-Off Ver
    Excel 2004
    Posts
    4

    Re: Vlookup formula not working

    That looks like it will do it. Thanks!

    I appreciate your time and effort. Have a great weekend...rb

  6. #6
    Registered User
    Join Date
    11-23-2010
    Location
    Wenatchee, WA
    MS-Off Ver
    Excel 2004
    Posts
    4

    Re: Vlookup formula not working

    one further step. the results I'm getting back have a bunch of empty cells between them. example, A1 returns a name and then the next name doesn't show up until A17. how do i get the results to bunch up?

  7. #7
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Vlookup formula not working

    The easiest way is to highlight the list of teachers in the first sheet, then go to Advanced Filter, check Unique Records Only, then Copy to Another Location. Choose the range in Sheet 2 for the location.

  8. #8
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,200

    Re: Vlookup formula not working

    Can't see the attachment here.

    Sheet2!A1 = Teacher Name

    In Sheet2!B1 enter, =COUNTIF(Sheet1!B:B,A1)

    Then A3 enter this ARRAY FORMULA (must hit CTRL+SHIFT+ENTER, rather than just ENTER) then copy down.

    =IF(ROWS(A$3:A3)<=$B$1,INDEX(Sheet1!$A:$A,SMALL(IF(Sheet1!$B$1:$B$1000=$A$1,ROW(Sheet1!$B$1:$B$1000)),ROWS(A$3:A3))),"")

    Will give the all students name where teacher = A1
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

+ 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