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
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,
Please see the file, hope it works
Bookmarks