use vlookup to compare today's date with a list of numbers

asked 2018-07-19 14:20:52 +0200

Mr Jelly gravatar image

Hello

I would like to use vlookup to compare todays date with a numerical value to return a value based on what day of the month it is.

I've attached a sample sheet, the formulas in question are in cells E2 and E3. I't s an account of monthly bills, how much and what day of the month they are paid.

Basically, if the number of todays date (i.e. it's the 19th today so "19") matches the number in column B (the day of the month the bill goes out) then I want to return the entries in column E (the amount of money due today) and F (the amount remaining for the rest of the month but as you can see I get the month total.

I can only assume that as I'm generating the figure in E1 with the TODAY() function then vlookup is not looking for a numerical value and so it's getting to the bottom of the list, not finding anything and returning the next entry up which is the last entry. Is there a way around this or am I missing something?

I'm pretty new to all this so any advise would be greatly appreciated. Thanks.

edit retag flag offensive close merge delete

Comments

What about =VLOOKUP( DAY( TODAY() ) ;<your range>;<column>;0)?

JohnSUN gravatar imageJohnSUN ( 2018-07-19 16:19:33 +0200 )edit

Thanks JohnSUN :-) That seems to work, tomorrow will tell me for sure when the date changes. No errors though so that's a good start. Thank you for your help.

Mr Jelly gravatar imageMr Jelly ( 2018-07-19 21:21:57 +0200 )edit