Vlookup and returning previous months data

Hello,

I am trying to see if the a # is in the current month and the previous month, if so return the value in column "S".

Data

1234 12-31-2014 18% <----- return this data
1233 12-31-2014 19%
1234 01-31-2015 20% ___ <----- Here

Do I have to use an if statement or can it all be done with a vlookup formula.

Current statement that I am using is =vlookup(g100,g2:g99,12,false) I am trying to prevent going back and locating the previous month (there are over 100k lines)

I am also using =vlookup(g100,g200:g300,12,false) to see if the same # shows up in the next month as well.

All help is greatly appreciated.

Another solution, this time one that doesn't use an array formula. It requires your dates are sorted oldest to newest.

Again, in my formula the columns contain data starting in row 2:
A: codes
B: dates
C: current period % value
D: prior period % value (desired result)

=IFERROR(INDEX($C1:C$2,MATCH(A2,INDEX($A1:A$2,IFERROR(MATCH(DATE(YEAR(B2),MONTH(B2)-1,0),$B1:B$2,0),1)):INDEX($A1:A$2,MATCH(DATE(YEAR(B2),MONTH(B2),1),$B1:B$2,1)),0)),"")

*This one does not have to be entered with CTRL + SHIFT + ENTER

example1.jpg

Post a workbook with some sample data and expected results, it makes it much easier to help you.

Originally Posted by sevanseriesta

I have added my worksheet to the original post.

Great, that will make it easier to help! I assumed you only had "month ending" dates in your date column, so my first two solutions might give you wonky answers.

Here's another ARRAY FORMULA that will return the "Balace (CAD)" for a given code from its prior month. This one is a little slow, so I would suggest trimming the ranges to your needs if you have a larger dataset.

Again, this must be entered with CTRL + SHIFT + ENTER

=IFERROR(INDEX($I:$I,MATCH(A2,IF($E:$E>DATE(YEAR(E2),MONTH(E2)-1,1),IF($E:$E<DATE(YEAR(E2),MONTH(E2),0),$A:$A,0),0),0)),"")

See example worksheet:
test1.xlsx

Originally Posted by sevanseriesta

So I should have explained better. The date that you are referencing does not matter in the whole equation. All I need to know on the test sheet is if the code ever appeared in the previous month (column L) I added the vlookup formula I was using in the original post to help understand kind of what I was manually doing.

So in your test sheet what I need is:

If "Column A" appears in January (column L), did it also appear in the previous month. If it did I was just having it return "Column L", but I am ok with it returning column L also, I hope that clears it up.

The following formula, entered as an ARRAY formula in cell M2 (enter by pressing CTRL + SHIFT + ENTER), will return the previous month if the listed code exists in the previous month. It will return blank if there is no exact match.

=IF(ISERROR(MATCH(A2&DATE(YEAR(L2),MONTH(L2),0),$A:$A&$L:$L,0)),"",DATE(YEAR(L2),MONTH(L2),0))

*See attached:
test1.xlsx

I may have overcomplicated the problem. Here is a formula that works better than the above example and is not an array formula.

=IF(COUNTIFS($A$2:$A$13,A2,$L$2:$L$13,DATE(YEAR(L2),MONTH(L2),0))>0,DATE(YEAR(L2),MONTH(L2),0),"")

See attached *
test1.xlsx