Soumik Dutta
Sometimes, we need to VLOOKUP in a time range to get the value according to our desire. In the article, we will demonstrate to you five quick methods to VLOOKUP with the time range in Excel. If you are also curious about it, download our practice workbook and follow us.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
5 Easy Methods to Vlookup with Time Range in Excel
To demonstrate the approaches, we consider a dataset of 10 employees of a company. Their name, department, and office joining time on a particular day are shown in our dataset. So, we can say our dataset is in the range of cells B5:B14.
Note
All the operations of this article are accomplished by using Microsoft Office 365 application.
1. Vlookup a Value from Time Range
In this method, we will use the VLOOKUP function to VLOOKUP a value with the time range. We have to input the Joining Time in cell D16, and the function will provide us with the name of the employee and the department in the range of cells D17:D18.
The steps of this process are given below:
📌 Steps:
- First, select cell D17.
- Now, write down the following formula into the cell.
=VLOOKUP($D$16,$B$5:$D$14,2,TRUE)
- Press Enter. As we don’t input the time, the function may show you an #N/A error. Don’t get panic about it. It will disappear when we input the time value in cell D16.
- Similarly, select cell D18 and write down the following to get the department value.
=VLOOKUP($D$16,$B$5:$D$14,2,TRUE)
- Press Enter.
- Finally, enter a valid time in cell D16 and press the Enter button.
- You will get both values at the desired cells.
Thus, we can say that our formula works perfectly, and we are able to do VLOOKUP with the time range in Excel.
Read More: Return Expected Value If Time Is Greater Than 1 Hour in Excel
2. Vlookup Two Time Ranges Using LOOKUP Function
In this process, we are going to use two different time ranges to VLOOKUP a value with the time range. For the second time range, we add the leaving time of our employees to our dataset. The LOOKUP function will help us to complete this method. We will input the times in the range of cells C17:C18 and get the output results in the range of cells E17:E18.
The steps of this method are given as follows:
📌 Steps:
- First of all, select cell E17.
- After that, write down the following formula into the cell.
=LOOKUP(2,1/($B$5:$B$14<=C17)/($C$5:$C$14>=C18),$D$5:$D$14)
- Then, press Enter. As we don’t input our desired time range in our input cell location, the function may return you an #N/A error. Don’t get panic about it. This error will disappear when we input the time values in the range of cells C17:C18.
- Similarly, select cell E18 and write down the following to get the department name.
=LOOKUP(2,1/($B$5:$B$14<=C17)/($C$5:$C$14>=C18),$E$5:$E$14)
- Again, press Enter.
- Now, enter a valid time range in the range of cells C17:C18.
- Press the Enter button.
- You will get both values at the desired cells.
So, we can say that our formula works effectively, and we are able to do VLOOKUP with the time range in Excel
Read More: Check If Time Is Between Two Times in Excel (2 Suitable Ways)
3. Find Single Output from Two Times
In this approach, we will use the INDEX, MATCH, and IF functions to do VLOOKUP with the time range. Here, we will get that value that lies between our input time. We have to input the times in the range of cells C17:C18 and get the output results in cells E17.
The steps of this approach are shown below.
📌 Steps:
- At first, select the merged cell E17.
- Next, write down the following formula into the cell.
=INDEX(C5:C14,MATCH(1,IF(B5:B14>C17,IF(B5:B14<C18,1)),0))
- Then, press Enter.
- You will see the name of that employee at our desired cell.
Hence, we can say that our formula works precisely, and we are able to do VLOOKUP with the time range in Excel.
🔎 Breakdown of the Formula
We are breaking down the formula for cell E17.
👉 IF(B5:B14<C18,1): The IF function checks whether the value of cell C18 is less than the time range. If the value is less the function returns 1. Otherwise, it returns FALSE. Here, the formula returns 1.
👉 IF(B5:B14>C17,IF(B5:B14<C18,1)): Here, the IF function checks whether the value of cell C17 is greater than the time range. If the logic is true, the function checks the second logic. Conversely, it returns FALSE. For this case, the formula returns 1.
👉 MATCH(1,IF(B5:B14>C17,IF(B5:B14<C18,1)),0): The MATCH function returns in which row both logic is true. The formula returns 5.
👉 INDEX(C5:C14,MATCH(1,IF(B5:B14>C17,IF(B5:B14<C18,1)),0)): Finally, the INDEX function shows the value of that row number, which number came from the MATCH function. Here, the formula returns Harmonie.
Read More: Return Value If Time Is Greater Than and Less Than a Range in Excel
4. Vlookup a Time Range with Multiple Criteria
In this case, we will apply multiple criteria to do a VLOOKUP with the time range and get the values. We are going to use the IF, COUNTIF, MATCH, and VLOOKUP function to finish the task.
The procedure of this method is explained below step-by-step:
📌 Steps:
- In the beginning, input your joining time. We input 9:30:00 AM.
- Now, insert a column on the left side of our dataset and entitled it Status.
- Then, select cell B5 and write down the following formula into the cell.
=IF(D5>=$C$17,"Late","Early")
- Next, press Enter.
- Similarly, add another column on the left side of the dataset and title it Lookup.
- After that, write down the following formula in the first cell.
=IF(C5="Late",COUNTIF($C$5:C5,"Late"),0)
🔎 Breakdown of the Formula
We are breaking down the formula for cell B10.
👉 COUNTIF($C$5:C5,”Late”): The COUNTIF function counts the ‘Late’ value in column C. Here, the formula returns 1.
👉 IF(C5=”Late”,COUNTIF($C$5:C5,”Late”),0): The IF function checks the value of the cell. If the cell value is ‘Late’ the COUNTIF function counts the value. Otherwise, it will show Zero (0). Here, the formula returns 1.
- Again, press Enter.
- Now, select the range of cells B5:C5 and double-click on the Fill Handle icon to copy the formula up to cell C14.
- You will notice that our first five value is Zero (0) and the last five values are addressed sequentially.
- Afterward, generate a new dataset like the image shown below.
- Then, select cell I5 and write down the following formula into the cell.
=VLOOKUP($H5,$B$4:$F$14,MATCH(D$4,$B$4:$F$4,0),FALSE)
🔎 Breakdown of the Formula
We are breaking down the formula for cell I5.
👉 MATCH(D$4,$B$4:$F$4,0): The MATCH function searches for the exact match of the column heading in the main dataset and provides us with the column number. Here, the formula returns 3.
👉 VLOOKUP($H5,$B$4:$F$14,MATCH(D$4,$B$4:$F$4,0),FALSE): The VLOOKUP function uses the value provided by the MATCH function and extracts the value from the main dataset. Here, the formula returns 9:30:00 AM.
- Press Enter.
- Drag the Fill Handle icon to your right to copy the formula up to cell K5.
- After that, select the range of cells I5:K5 and drag the Fill Handle icon up to cells K9.
- You will see all the values at our desired location.
Therefore, we can say that our formula works successfully, and we are able to do VLOOKUP with the time range in Excel.
Read More: Return Value If Time Lies Between Multiple Time Ranges in Excel
5. Return Value If Time Is Between a Range
In this last process, we will VLOOKUP data from another dataset to our dataset using the VLOOKUP function. Our main dataset is in the range of cells B5:D14 and the dataset from where we will import the values is in the range of cells G5:H7. Moreover, we are going to display those data in column E.
The steps of this procedure are described as follows.
📌 Steps:
- Firstly, select cell E5.
- Now, write down the following formula into the cell.
=VLOOKUP(B5,$G$5:$H$7,2,TRUE)
- Then, press Enter.
- After that, double-click on the Fill Handle icon to copy the formula upto cell E14.
- You will notice all the values are extracted at our desired location.
Finally, we can say that our formula works perfectly, and we are able to do VLOOKUP with the time range in Excel.
Read More: Excel If Time Is Between Range Then Return Value
Conclusion
That’s the end of this article. I hope that this article will be helpful for you and you will be able to do VLOOPUP with the time range in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.
Don’t forget to check our website, ExcelDemy, for several Excel-related problems and solutions. Keep learning new methods and keep growing!
