VLOOKUP when Lookup Data is not in the left column

[jhg05003]

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:

Notice the 'Y' for the 1500m is in the 4th column.

Here is where I need the surname of the student to appear.


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

[Pecoflyer]

Cross post http://www.excelforum.com/excel-form...ml#post3563897

[bgoree09]

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,

[Azumi]

Please see the file, hope it works