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

haribabu

New Member
Joined
Feb 5, 2014
Messages
7
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
ItemCreated onCategory (OUTPUT)
A4/2/2020Cat1
B13/2/2020#N/A
C1/1/2020Cat2
D5/6/2019#N/A

Table 2
ItemCategoryLower LimitUpper Limit
ACat1
3/30/2020​
4/12/2020​
ACat2
12/29/2019​
1/28/2020​
ACat3
12/22/2019​
1/1/2020​
ACat4
5/6/2018​
5/6/2019​
BCat1
3/30/2020​
4/12/2020​
BCat2
12/29/2019​
1/28/2020​
BCat3
12/22/2019​
1/1/2020​
BCat4
5/6/2018​
5/6/2019​
CCat1
3/30/2020​
4/12/2020​
CCat2
12/29/2019​
1/28/2020​
CCat3
12/22/2019​
1/1/2020​
CCat4
5/6/2018​
5/6/2019​
DCat1
3/30/2020​
4/12/2020​
DCat2
12/29/2019​
1/28/2020​
DCat3
5/6/2018​
5/6/2019​
DCat4
12/29/2019​
1/28/2020​
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
How about
Book1
ABC
1ItemCreated onCategory (OUTPUT)
2A02/04/2020Cat1
3B13/02/2020 
4C01/01/2020Cat2
5D06/05/2019Cat3
Sheet1
Cell Formulas
RangeFormula
C2:C5C2=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)),"")
 
Upvote 0
=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
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top