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.

**Table of Contents**Expand

## How to Vlookup with Time Range in Excel: 5 Easy Methods

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 the 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 in 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 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.

### 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 in 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 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

### 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:**

- First, select the merged cell
**E17**. - Next, write down the following formula in 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 in 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**.

### 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** functions 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 for 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 in 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.

### 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 in 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 up to 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.

**Download Practice Workbook**

Download this practice workbook for practice while you are reading this article.

## 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. Keep learning new methods and keep growing!

## Related Articles

- How to Use VLOOKUP to Find a Value That Falls Between a Range
- How to Use VLOOKUP If Condition Lies Between Multiple Ranges in Excel

**<< Go Back to VLOOKUP a Range | Excel VLOOKUP Function | Excel Functions | Learn Excel**