Formula To Go Through Each Row And If The 2nd Column Is Yes Then Return The First Column

I have a collection of data like the following:
Week1 | yes
Week2 | no
Week3 | no
Week4 | yes
Week5 | no
...
Week28 | yes

I need a formula to go through each row and if the 2nd column is yes then return the first column. I could then copy and paste this formula into a the next cell, add an argument saying the returned value cannot be the same as the previous ones, and so on. Essentially this is a series of nested IF statements. However, Excel prevents more than 7 statements from being nested, when I need all 28 to be searched. I cannot think of a way to get VLOOKUP, INDEX & MATCH, or defined names to make this work, because it needs to step through each row.