VLOOKUP Formula (Lookup value and Lookup vector are in the same range )

I'm using several helper columns. If you want, you could substitute formula segments and keep substituting the parts back until you don't need the helper columns but then you would wind up with some very incomprehensible formulas.

First. I converted the data into an Excel data. This means that you can add as much data as you want and the formulas will still work. It should even work from month to month.

Enter the current month into cell I1. The previous month is automatically calculated in Cell I2.=IF(1*RIGHT(I1,2)>1,LEFT(I1,4)&TEXT(1*RIGHT(I1,2)-1,"0#"),LEFT(I1,4)-1&"12") - basically this formula captures the year and subtracts one from the month unless the month is 1 in which case it subtracts one from the year and the month becomes 12.

Then in cells J1 and J2, I use the match command to find out where these values first occur in the list. I need this figure for the offset command,. Cells K1 and K2 contain the number of rows for the current month and last month.

The next thing happens behind the scenes I use Formula > Name Manager to define two named dynamic ranges.
Last_Month =OFFSET(TableData[[#Headers],[Register Number]],Sheet1!$J$2,0,Sheet1!$K$2,2)
This_Month =OFFSET(TableData[[#Headers],[Register Number]],Sheet1!$J$1,0,Sheet1!$K$1,2)

The offset command takes 5 parameters:
- Start here - in both cases this is the Header Cell of the Register Number
- Go down - I get these values from cells J1 and J2.
- GO Right - a hard coded zero columns
- Give me x rows - these are cells K1 and K2
- Give me y columns - a hard coded 2

One of the helper column in Column E to determine if the Month is This Month or Last Month or neither.

That value helps me determine which VLOOKUP to use in Column D.

It also helps me interpret whether #N/A is Deleted or New.

The named ranges only work if the months are contiguous: that is all the 202103 are with each other and all the 202104 are with each other, etc.