Vlookup with Time Range in Excel (5 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

Using VLOOKUP function to get the employee name

  • 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.

Using VLOOKUP function to get the department

  • Finally, enter a valid time in cell D16 and press the Enter button.
  • You will get both values at the desired cells.

VLOOKUP a Value from Time Range

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.

Using LOOKUP functions to VLOOKUP employee's name

  • 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.

Using LOOKUP functions to VLOOKUP department name

  • 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.

VLOOKUP Two Time Ranges Using LOOKUP Function

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.

Using IF, MATCH, and INDEX functions to VLOOKUP data from time range

  • You will see the name of that employee at our desired cell.

Find Single Output from Two Times to do VLOOKUP with Time Range

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.

Using IF function to take decision for VLOOKUP

  • 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.

Applying IF and COUNTIF function to convert the decision into numeric form

  • 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.

Using VLOOKUP function to do the VLOOKUP operation

  • Drag the Fill Handle icon to your right to copy the formula up to cell K5.

Extract the data row-wise

  • After that, select the range of cells I5:K5 and drag the Fill Handle icon up to cells K9.

Extracting all data using VLOOKUP function

  • You will see all the values at our desired location.

VLOOKUP a Time Range with Multiple Criteria

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.

Using VLOOKUP function complete the VLOOKUP operation with time range

  • 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.

Return VLOOKUP Value If Time Is Between a Range

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!

Soumik Dutta

Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo