HI,
I am wanting to create a formula in excel that will search for a pattern in text across multiple cells on the 1 row. I have 50 cells in the range (all on the 1 row) that will either have a Y or an N in each cell. I want excel to be able to search this range of cells to look for the first instance of the pattern Y, N, N, N. Each cell will only have 1 letter in it, so it needs to look at all the cells for the pattern. The pattern could possibly be repeated in the same row, so it needs to identify the first instance of this pattern. Once it identifies the cell with the 'Y' in it prior to the 3 consecutive cells with 'N' in them, I want it to return the value of the cell in row 1 of that same column.

Basically, I am trying to analyse the results of a spelling test. Once the student gets 3 incorrect results in a row, the test is over and the last correct word places them at the right level for further spelling practice. The number of the word on the spelling test then correlates with an alphabetised list of words. So for example, if the 10th word was the last correct word before 3 consecutive errors, this would place the student halfway through grade 2 academically and assign them list 'I' to provide appropriate spelling words to practice and learn. This is easy to see if it is just a few students that I am analysing, but I need to be able to do this quickly for several hundred students.

I have tried using the SEARCH, FIND, MATCH, IF, LOOKUP functions in excel, but I can't get it to search for the pattern across multiple cells, only individual cells. I haven't even tried the second part of the formula to return the value of row 1 when it finds the Y, and I am hoping someone out there is clever enough to help me figure this one out. I have attached a binary version of my spreadsheet if that helps.

formula request ynnn.xlsb

Thanks and sorry for long post.