Find row number of next instance of specific value

Question

I have a column of days of the week in an Excel 2008 spreadsheet. In another column, I want to execute code that involves the Friday in the current row and its following Friday further down in the column. The problem is, it's five years of data that lists the days consecutively for most weeks, but sometimes there's a day missing in the week due to holidays. Hence I can't reference a fixed number of cells away to compare data from Friday to Friday.

So I'm looking for a means of doing the following pseudo-code:

=if(xcell = "Friday" and the_next_cell_named_Friday_down_in_the_list = "Friday", do "code that works with the row xcell is in and the row the next Friday cell is in")

I know there's a way of doing it, but I can't quite see how to execute it.

(If it's not too hard to answer this as well, I also want to be able to do the same thing as above, except check the Friday after the next (every two weeks).)

Answer 1

Use VLOOKUP to find the next "Friday" down in the column of days. E.g.: if column of days is column A, and the row you are currently on is 4, while the last row in column A is 86523, then the correct formula to find the next "Friday" is:

VLOOKUP("Friday", A5:A86523, 1, FALSE)

However this will give you back "Friday" if there is a next "Friday" after row 4, and will give you #N/A error if there isn't - not very useful. You can manipulate table_array (A5:A86523), or index_number (1) to get back something more useful.
For help on the VLOOKUP function click here.

Answer 2

=match("xyz",a1:a3,0)

appears to do it

Answer 3

OP's own answer will not "copy down" so I suggest:

=IF(A2="Friday",MATCH("Friday",A3:A$20,0)+ROW(),"")  

Extend range limit to suit.