Vlook Months (dates) to search nearest month in the past and return value

Hi,

Vlook Months (dates) to search nearest month in the past and return value.

Please refer the attached file.

'Nearest month in the past' means previous month, right? Or I don't understand something.

yes closest Previous month in the list of months available

Based on your in file description..

I created

In E6:

Please Login or Register  to view this content.

In D6 I changed formula to

Formula:

Please Login or Register  to view this content.

(edate in D-columnnot a crititcal change, more a case of my preferrence as it is easier to read / build)



you may have to play with the "<"&EDATE(D6;1) condition in the formula to get what you want exactly. I was a bit confused by the request in the post and instructions in the file itself.
I choose to use EDATE but formula EOMONTH might also be an option in this case depending on the date input in the actual sheet.

see attachement for working solution

So results should be like below:

Year Month Number Completed Till Date
2019 Mar-19 28
2019 Apr-19 69
2019 May-19 111
2019 Jun-19 152
2019 Jul-19 193
2019 Aug-19 235
2019 Sep-19 276
2019 Oct-19 317
2019 Nov-19 359
2019 Dec-19 400

for each month value from table Status for previous month, right?

Hi Roel,

We are close.

I don't want a sumif .. if I am searching for May-19 it should show the value in column C of Status Worksheet next to the month earlier than May-19.

for example when search for May-19 value should return as 111 ( which is the count given for the nearest past month Apr-19)

Originally Posted by KOKOSEK

So results should be like below:

Year Month Number Completed Till Date
2019 Mar-19 28
2019 Apr-19 69
2019 May-19 111
2019 Jun-19 152
2019 Jul-19 193
2019 Aug-19 235
2019 Sep-19 276
2019 Oct-19 317
2019 Nov-19 359
2019 Dec-19 400

for each month value from table Status for previous month, right?

Your understanding on my need is correct

Please try at E6 and drag down

=VLOOKUP(D6-1,Status!$B$2:$C$25,2)

Originally Posted by Bo_Ry

Please try at E6 and drag down

=VLOOKUP(D6-1,Status!$B$2:$C$25,2)

This works as expected till I reach Dec-19, When the same formula goes to Jan-20 it shows blank. it should show 400

Please try again E6

=LOOKUP(2,1/(D6>Status!$B$2:$B$25)/(Status!$C$2:$C$25<>""),Status!$C$2:$C$25)

Originally Posted by Bo_Ry

Please try again E6

=LOOKUP(2,1/(D6>Status!$B$2:$B$25)/(Status!$C$2:$C$25<>""),Status!$C$2:$C$25)

You Rock Thats all I wanted .. Thank you so much

Happy to help.
Thank you for the feedback and mark topic Solved

Or something more clear to understand (E5):

Formula:

Please Login or Register  to view this content.