Formula to return previous month balance (EDATE) not working

[thematrix05]

I have a 2 column table with month and balance.

My formulas to return previous month balance isn't working.


=VLOOKUP(EDATE(TEXT(B1,"mmm"),-1),A6:B17,2)
=SUMIFS(B6:B17,A6:A17,EDATE(B1,-1))

Thank you in advance.

[etaf]

probably be a better way to do this

But the months you have are NOT date - just text
so , you cna convert the AUG - to a real date using
DATEVALUE("1/"&B1&"/23")
that makes it the 1st of month
now take 1 away to last month
DATEVALUE("1/"&B1&"/23")-1
Now change to text to get JUL
DATEVALUE("1/"&B1&"/23")

TEXT(DATEVALUE("1/"&B1&"/23")-1,"MMM")

then add to the lookup

=VLOOKUP(TEXT(DATEVALUE("1/"&B1&"/23")-1,"MMM"),A6:B17,2)

[thematrix05]

That worked! Thank you!

[etaf]

there maybe an issue - as you have dtes and text , not sure it works correctly
may need to change the lookup to dates if that is wht you want

[etaf]

if you change the list to real dates and format as mmm
then change the lookup to

=VLOOKUP((DATEVALUE("1/"&B1&"/23")-1),A6:B17,2)

now it will work correctly and if JAn is entered return N/A

as you are using nearest lower value - it picks up the 1 of month

could change so edate , bit more involved using false at end of lookup