Zehad Rian Jim
This tutorial will demonstrate how to return the value if the date is within range in excel. When delivering products or submitting important projects, it is essential to set a return value within a certain date. It clears out if the employee or student is doing his duties with attention or not. So, it is important to learn this process.
Download Practice Workbook
You can download the practice workbook from here.
5 Easy Methods to Return Value If Date Is Within a Range in Excel
If you follow the steps correctly, you should learn how to return the value if the date is within range in the excel cell on your own. The methods are:
1. Using VLOOKUP Function
In this case, our goal is to return the value if the date is within range by using the VLOOKUP Function. VLOOKUP Function is mainly used when you have to find a table or some info or any data by row. The steps of this method are.
Steps:
- To begin with, arrange a dataset like the below image.
- In addition, insert the following formula in cell D15.
=VLOOKUP(D14,B5:F12,5,TRUE)
- Finally, you will get the below result.
2. Use of INDEX Function
We aim to return the value if the date is within the range by using the INDEX function. The INDEX function returns a value within a range. We have to follow the below steps to learn this whole method.
Steps:
- First, arrange a dataset like the below image.
- Next, insert the following formula in cell C15.
=INDEX(F5:F12,MATCH(1,IF(B5:B12>B15,IF(B5:B12<B16,1)),0))
- Finally, you will get the below result.
3. Applying Nested IF Function
Now, our goal is to return the value if the date is within the range by using the Nested IF Function. The NESTED IF Function gives a logical comparison within the proper date range. We have to follow the below steps to learn this whole method.
Steps:
- At first, arrange a dataset like the below image.
- Second, insert the following formula in cell E5.
=IF(D5>=B5,IF(D5<=C5,"In Range","Out of Range"),"Out of Range")
- Third, you will get the result for the cell and then use the Fill Handle option.
- Last, you will get the below result.
4. Combining IF & AND Functions
We aim to return the value if the date is within the range by using the combination of both IF and AND Functions. The If function gives logical comparison, and the And function returns if the condition is true or false. We have to follow the below steps to learn this whole method.
Steps:
- First, arrange a dataset like the below image.
- Next, insert the following formula in cell E5.
=IF(AND(D5>=B5,D5<=C5),"In Range","Out of Range")
- After that, you will get the result for the cell and then use the Fill Handle option.
- Finally, you will get the below result.
🔎 How Does the Formula Work?
- AND(D5>=B5, D5<=C5): This portion presents the selected cell to the conditions that will be applied.
- IF(AND(D5>=B5, D5<=C5), “In Range”, “Out of Range”): This portion presents the conditions of the function.
Read More: Return Expected Value If Date Is Between Two Dates in Excel
5. Utilizing LOOKUP Function
In this case, our goal is to return the value if the date is within range by using the LOOKUP Function. The LOOKUP Function is mainly used when you have to find a table or some info or any data by row. We have to follow the below steps to learn this whole method.
Steps:
- To begin with, arrange a dataset like the below image.
- In addition, insert the following formula in cell D15.
=LOOKUP(2,1/($B$5:$B$12>=C15)/($D$5:$D$12<=C16),$C$5:$C$12)
- Finally, you will get the below result.
How to Compare Dates in Excel
In this case, our goal is to return the value if the date is within the range using the INT Function. This function compares two certain data and gives results accordingly. We have to follow the below steps to learn this whole method.
Steps:
- First, arrange a dataset like the below image and insert the following formula in cell D5.
=INT(B5)=INT(C5)
- Next, you will get the below result.
Things to Remember
- If there remains any error in any formula, excel will show an error.
- While using the formulas, you should give proper attention to the cell you are using. Otherwise, the formula won’t give the desired result.
Conclusion
Henceforth, follow the above-described methods. Hopefully, these methods will help you to return the value if the date is within range in excel. We will be glad to know if you can execute the task in any other way. Follow the ExcelDemy website for more articles like this. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our best to solve the problem or work with your suggestions.
