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**hide

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