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

Hi,


Please find the attached sample file. How to extract status from previous month to current month and vice versa using VLOOKUP formula. Previous month data and Current month data are in the same range.

Explanation :

* Need to extract status from Current month (and) Need to extract status from Previous month
i) in first scenario, B2:B21 are lookup values , B22:C40 is Lookup Vector
ii) in Second scenario, B22:B40 are lookup values , B2:C21 is Lookup Vector

* If current month return value (result range: D22:D40) is #N/A then we should consider that error value as "New"
* If Previous month return value (result range: D2:D21) is #N/A then we should consider that error value as "Deleted"


Any help would greatly appreciated.


Note: For your reference, I added sample output data in the attached file(N2:Q41)


Regards
Raj Kumar

Originally Posted by b_raj_kumar

Previous month data and Current month data are in the same range.

But they are not in the same range, according to your description.

Put this formula in D2 and fill down to D21

Formula:

=IFERROR(VLOOKUP(B2,$B$22:$C$40,2,0),"Deleted")

Put this formula in D22 and fill down to D40:

Formula:

=VLOOKUP(B22,$B$2:$C$21,2,FALSE)

Named ranges:
current_month =INDEX(Sheet1!$A:$A,current_month_row):INDEX(Sheet1!$C:$C,MATCH(9000000000,Sheet1!$C:$C))
current_month_row =Sheet1!$T$1
Previous_Month =Sheet1!$A$2:INDEX(Sheet1!$C:$C,current_month_row-1)

---

D2:

=IFERROR(CHOOSE(((RIGHT(A2,2)*1)=MONTH(TODAY()))+1,
INDEX(current_month,MATCH(B2,INDEX(current_month,0,2),0),3),
INDEX(Previous_Month,MATCH(B2,INDEX(Previous_Month,0,2),0),3)),"Deleted")

find start of current month (T1):

=MIN(IF(A2:INDEX(A:A,COUNTA(A:A))<>A2,ROW(A2:INDEX(A:A,COUNTA(A:A)))))

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.