looking up cell value based on whether its the current month, or next month.

[BigPapaMurf]

Hi folks, new here. I'm looking to update a Budget spreadsheet I've modified over the years and right now I'm stuck on updating one formula..

Summary sheet
My table has 3 columns. Column B looks up column A's matching cell on my sheet named Personal Budget and shows the value for the current month. Column C references due dates I placed in a table on a sheet called Schedule.

Column A = text
Columb B =VLOOKUP($A4,'Personal Budget'!$A$4:$M$157,MONTH(TODAY())+1,0)
Column C =EDATE(TODAY()-DAY(TODAY())+(VLOOKUP($A4,Schedule!Fixed_Expenses,2,0)),--(DAY(TODAY())>(VLOOKUP($A4,Schedule!Fixed_Expenses,2,0))))


The Personal Budget sheet (Personal Budget'!$A$4:$M$157) has rows that match Summary sheet's Column A and columns that = each month of the year (January....December)

So, for example, if today's date is 4/10 I have a bill due on 4/26 currently, it will display the value for month 4 for the entire month, until 5/1. (because on 5/1 it looks for the cell in month 5)

What I would like it to do is change once the due date has hit and not wait for it to change on the first day of the month to match that month / column

The perfect formula would combine these....
If $C4 > today and $C4 =month(today), then display the cell value found via lookup on Personal Budget sheet month(today)
If $C4 > today and $C4 is next month, then display the cell value found via lookup on Personal Budget sheet next month


Any help would be greatly appreciated.

Sample attached.

Thanks
Murph

[benishiryo]

welcome to the forum, Murph. you didn't explain what should happen if date is lesser or equals to today. but change the date in Personal Budget sheet to be jan2020 in cell B2. copy it across to M2.

if you don't like the presentation, custom format to mmmm.

then in Summary sheet B3, use:
=IF(C3>TODAY(),SUMIF('Personal Budget'!A:A,A3,INDEX('Personal Budget'!B:M,,MATCH(EOMONTH(C3,-1)+1,'Personal Budget'!$B$2:$M$2,0))),"nothing")

change "nothing" to what you want if C3 <= Today

[BigPapaMurf]

Benishiryo that did work thanks.

[Estevaoba]

Try this in B3 of summary sheet and copy down:

=VLOOKUP($A3,'Personal Budget'!$A$2:$M$14,1+MATCH(MONTH(C3),COLUMN('Personal Budget'!$A$2:$M$2),0),0)

Good luck!

[BigPapaMurf]

this also worked! thanks

[Estevaoba]

You're welcome!
Glad to help.