Finding the number of rows between VLOOKUP values

[sgcannon]

Hi,

I have a "master" sheet, the first column of which contains every date that the stock market is open.

Somewhere in the middle of each month there is one day when stock market options expire. I have an "exp_symb" sheet that lists the date of option expiration for each month in the E column.

What I want to do is calculate the percentage of trading days that today is relative to the previous options expiration date and the next options expiration date. Because the option expiration dates are based on the day of the week ie. the third Wednesday, the number of trading days will vary from month to month.

For example if today was one of the options expiration dates, the function would return 0% because I am 0% of the way to the next options expiration date. Suppose that there are 20 trading days between this option expiration and the next option expiration day, then tomorrow the function would return 5% and the following day, 10%.

What I have so far is =VLOOKUP(TODAY(),'Exp Symb'!$E:$E,1,TRUE). This looks at ExpSymb and returns the date of the most recent options expiration.

The second step would be to find the date of the next options expiration date. I want to say "look at the box returned in that previous function and go to the box right below it and return that date."

Finally, I want to subtract, not the days between these two expiration dates, but the number of trading days between the two dates. So in english I'd say, "find the previous expiration date in column A of the master sheet and note the row number. then find the next expiration date also in column A of the master sheet and note its row number. Now subtract the first from second. That will give the number of rows between the previous and next options expiration dates which will be equal to the number of trading days between the two dates."

Any help would be greatly appreciated.

Thanks,

Steve

 

[Joyner]

Hello, Based on what you have discussed, I think you are looking for something like this to calculate the days:

=MATCH(INDEX('Exp Symb'!$E:$E,MATCH(TODAY(),'Exp Symb'!$E:$E,1)+1),'Master Sheet'!A:A,0)-MATCH(INDEX('Exp Symb'!$E:$E,MATCH(TODAY(),'Exp Symb'!$E:$E,1)),'Master Sheet'!A:A,0)

Note that this simply subtracts the relative position of one date from another - you may want to add 1 (one) to the result depending on how you want to treat the days, inclusive or not.

Although you have used entire column references E:E, and I have shown them here for ease, I would recommend you use the actual data range references.

 

[sgcannon]

So the match-index thing totally worked. Since I was familiar with neither of those functions, I suspect I could've spent a month trying to figure out how to do this. Thank you SO much!

The other thing that's nice is that I've always used vlookup but as I was learning about match-index there seems to be some advantages of that vs vlookup that I will have to figure out in the future.

thanks again,

Steve

 

[sgcannon]

WOW!!!!

I have used vlookup and found that when I insert columns, everything gets screwed up. After having this problem 3 or 4 times I made a special cell where I would manually enter the number of the column to use with vlookup. If I inserted a column, I would add one to that special cell. I thought, that is the stupidest, lamest thing in the world. Now I read:

"The greatest benefit of using INDEX MATCH over VLOOKUP is the fact that, with INDEX MATCH, you can insert columns in your table array without distorting your lookup results."

THANK YOU!

Steve

 

[Joyner]

You are welcome and yes both INDEX and MATCH are two great functions to get to know well. VLOOKUP will always have its place for some simply cases. LOOKUP (the original) is also a good function to learn.