Finding the number of rows between VLOOKUP values

sgcannon

Board Regular
Joined
Jan 28, 2016
Messages
55
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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Joyner

Well-known Member
Joined
Nov 15, 2005
Messages
1,202
Hello, Based on what you have discussed, I think you are looking for something like this to calculate the days:


Code:
=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.
 
Last edited:

sgcannon

Board Regular
Joined
Jan 28, 2016
Messages
55
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

Board Regular
Joined
Jan 28, 2016
Messages
55
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

Well-known Member
Joined
Nov 15, 2005
Messages
1,202
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,216
Messages
5,594,886
Members
413,947
Latest member
gizmolucy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top