vlookup to check condition if DATE is between two dates (in two separate columns)

[haribabu]

Hello
I am trying to find if a date is between two dates then find the row and category against those dates.
Table 1 -Column C is my goal. Any excel formula to arrive at this solution?

• For Item A- created on is 4/2/2020 so I need to check Table 2 for Item A and see if 4/2/2020 is in between Lower Limit column & Upper Limit Column. In this case Item A, Category Cat1 has range 3/30/2020 to 4/12/2020 and 4/2/2020 is in between these two dates hence Cat 1 is my solution

Table 1

Item	Created on	Category (OUTPUT)
A	4/2/2020	Cat1
B	13/2/2020	#N/A
C	1/1/2020	Cat2
D	5/6/2019	#N/A

Table 2

Item	Category	Lower Limit	Upper Limit
A	Cat1	3/30/2020​	4/12/2020​
A	Cat2	12/29/2019​	1/28/2020​
A	Cat3	12/22/2019​	1/1/2020​
A	Cat4	5/6/2018​	5/6/2019​
B	Cat1	3/30/2020​	4/12/2020​
B	Cat2	12/29/2019​	1/28/2020​
B	Cat3	12/22/2019​	1/1/2020​
B	Cat4	5/6/2018​	5/6/2019​
C	Cat1	3/30/2020​	4/12/2020​
C	Cat2	12/29/2019​	1/28/2020​
C	Cat3	12/22/2019​	1/1/2020​
C	Cat4	5/6/2018​	5/6/2019​
D	Cat1	3/30/2020​	4/12/2020​
D	Cat2	12/29/2019​	1/28/2020​
D	Cat3	5/6/2018​	5/6/2019​
D	Cat4	12/29/2019​	1/28/2020​

 

[Fluff]

How about

Book1
	A	B	C
1	Item	Created on	Category (OUTPUT)
2	A	02/04/2020	Cat1
3	B	13/02/2020
4	C	01/01/2020	Cat2
5	D	06/05/2019	Cat3
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=IFERROR(INDEX(Sheet2!$B$2:$B$17,AGGREGATE(15,6,(ROW(Sheet2!$B$2:$B$17)-ROW(Sheet2!$B$2)+1)/((Sheet2!$A$2:$A$17=A2)*(Sheet2!$C$2:$C$17<=B2)*(Sheet2!$D$2:$D$17>=B2)),1)),"")

 

[haribabu]

=IFERROR(INDEX(Sheet2!$B$2:$B$17,AGGREGATE(15,6,(ROW(Sheet2!$B$2:$B$17)-ROW(Sheet2!$B$2)+1)/((Sheet2!$A$2:$A$17=A2)*(Sheet2!$C$2:$C$17<=B2)*(Sheet2!$D$2:$D$17>=B2)),1)),"")

Brilliant

 

[Fluff]

You're welcome & thanks for the feedback