Results 1 to 2 of 2

Thread: Match or index???

  1. #1

    Match or index???



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have attached a sample from aspeadsheet I am working on. The option reply sheet has '1' entered if the student has opted to take this course. On the other subject sheets I would like the names who have opted for that subject to appear. Stuidents can opted for multiple subjects. Any support will be great I have tried Match and Index functions but it is difficult as students can opt for more than one.

    Many thanks
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    To avoid array formulas, I would add helper columns to your Option Reply Sheets (these columns can be hidden, if desired).

    In Option Replies sheet, starting at E2 enter formula:

    =$A2&COUNT(B$2:B2)

    copy it across a number of columns equal to the number of subject columns. Then copy down as far as you want.

    Now in each tab you would use the formula:

    =IFERROR(INDEX('Option replies'!A:A,MATCH("*"&ROWS($A$4:$A4),'Option replies'!$E:$E,0)),"")

    copied down as far as you need to get all names.

    Note: In each sheet, the lookup range, 'Option replies'!$E:$E, will need to be change to correspond to appropriate column on first sheet.


Posting Permissions

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