Results 1 to 4 of 4

Thread: VLOOKUP when Lookup Data is not in the left column

  1. #1

    VLOOKUP when Lookup Data is not in the left column



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

    Hello,

    I am try to create a function where the Surname of a student is returned when the value of a non-left column is "y". I know I could use a VLOOKUP if the "y" was in the left most column but I have many different columns with Y's in them and they will keep changing so this is not an option.

    Let me show you an example:
    Here is the sheet where teachers put in the students who are participating in each event:
    Click image for larger version. 

Name:	Screen Shot 2014-01-29 at 10.04.09.png 
Views:	8 
Size:	17.1 KB 
ID:	1992
    Notice the 'Y' for the 1500m is in the 4th column.

    Here is where I need the surname of the student to appear.
    Click image for larger version. 

Name:	Screen Shot 2014-01-29 at 10.04.15.png 
Views:	6 
Size:	21.3 KB 
ID:	1993

    The complete excel file is also attached.

    In fact I need the surname of both students who have 'Y's to appear in each column but I was hoping to cross that bridge next.

    Thanks in advance
    JG
    Attached Files Attached Files

  2. #2
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,512
    Articles
    0
    Excel Version
    2010 on Xubuntu

  3. #3
    Good morning,

    Use index-match. = INDEX ( --answer range **in this case Surname** --, match( "Y" , --lookup range **so for the 1500 meter section it would be D47, and so on ** -- , 0 )). Repeat this for each of your sections.

    Best of luck,

  4. #4
    Acolyte Azumi's Avatar
    Join Date
    Jan 2014
    Location
    Indonesia
    Posts
    29
    Articles
    0
    Excel Version
    2010
    Please see the file, hope it works
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

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