The article will show you some basic methods on how to use **VLOOKUP **to find a value that falls between a range. We use the** VLOOKUP** Function to see particular information about data that is in an Excel chart or table.

In the dataset, we have information about some employees- their **Names**,** Joining Date**, **Entry Time **in the office, **Salary**, and **Bonus **according to **Salary Range**.

## How to Use VLOOKUP to Find a Value That Falls Between a Range: 6 Simple Ways

**1. Using VLOOKUP to Find Bonus That Falls Between a Salary Range**

In this section, we will determine the bonuses of these employees by using the **VLOOKUP Function**. In the dataset, we fixed the bonuses according to the salary range. For instance, those who have salaries in the range of 2000$ to 2999$ will get a bonus of 200$. Employees who have salaries in the range of 3000$ to 3499$ will get a bonus of 250$ and so on. Let’s go through the process below.

**Steps:**

- First, we made some modifications as we don’t need the
**Entry Time**and**Joining Date**in this section.

- Now, type the following formula in cell
**D5**.

`=VLOOKUP(C5,$F$5:$G$10,2,TRUE)`

Here, the **VLOOKUP** Function looks for the **Salary **in cell **C5 **in the range of **F5:G10**. As we want to determine the bonuses that are in the **2nd column **of **F5:G10**, we set **2 **as the **col_index_num**. We look for the salaries in the Range column and we want to find them from the range F5:F10 which will be an **approximate_match**, and for that reason, we set **[range_lookup] **as **TRUE**. We didn’t want to change the range reference **F5:G10**, so we used an **absolute cell reference**.

- Press the
**ENTER**button and you will see the bonus for**Shane**who is mentioned in cell**B5**.

- After that, use the
**Fill Handle**to**AutoFill**the lower cells.

You will see the bonuses for all employees. Thus you can use **VLOOKUP **to find a value that falls between a range.

**2. Applying VLOOKUP to Find Status Between a Time Range**

We can also use the** VLOOKUP** Function to find time values from a time range and thus determine the status of the employees for a day whether they were late or not. Let’s follow the description below.

**Steps:**

- First, we made some modifications as we don’t need the
**Salary**and**Joining Date**in this section.

- Now, type the following formula in cell
**D5**.

`=VLOOKUP(B5,$F$5:$G$7,2,TRUE)`

Here, the **VLOOKUP** Function looks for the **Entry Time **in cell **B5 **in the range of **F5:G7**. As we want to determine the **Status **which is in the **2nd column **of **F5:G7**, we set **2 **as the **col_index_num**. We look for the **Entry Time **in the Time column and we want to find them from the range F5:F7 which will be an **approximate_match**, and for that reason, we set **[range_lookup] **as **TRUE**. We didn’t want to change the range reference** F5:G7**, so we used an **absolute cell reference**.

- Press the
**ENTER**button and you will see the**Entry Time Status**of**Shane**.

- After that, use the
**Fill Handle**to**AutoFill**the lower cells.

You will see the statuses of all employees. Thus you can use **VLOOKUP **to find a value that falls between a range.

**3. Utilizing VLOOKUP Function to Find Bonus Entry That Falls Between a Date Range**

**VLOOKUP** function can also be useful to find date values from a date range. Suppose the employees who join in a particular date range get bonuses otherwise, they don’t. For instance, if an employee joins between **April **and **June**, he will get a bonus. We can set a formula to use the date range to find an employee whether he gets a bonus or not. Let’s follow the description below.

**Steps:**

- First, we made some modifications as we don’t need the
**Salary**and**Entry Time**in this section.

- Now, type the following formula in cell
**D5**.

`=VLOOKUP(C5,$F$5:$G$8,2,TRUE)`

Here, the **VLOOKUP** Function looks for the **Joining Date **in cell **C5 **in the range of **F5:G8**. As we want to determine the **Bonus Entry **which is in the 2nd column of **F5:G8**, we set 2 as **col_index_num**. We look for the **Joining Date **in the **Range **column and we want to find them from the range F5:F8 which will be an **approximate_match**, and for that reason, we set **[range_lookup] **as **TRUE**. We didn’t want to change the range reference** F5:G8**, so we used an **absolute cell reference**.

- Press the
**ENTER**button and you will see the**Bonus Entry Status**of**Shane**.

- After that, use the
**Fill Handle**to**AutoFill**the lower cells.

You will see the bonus entry statuses of all employees. Thus you can use **VLOOKUP **to find a date value that falls between a date range.

**4. Using VLOOKUP and Combined Functions to Find a Value That Falls Between a Range**

We can also determine the bonuses of these employees by using the combination of **VLOOKUP**, **INDIRECT**, **MATCH**, and **ROW** Functions. In the dataset, we fixed the bonuses according to the salary range. For instance, those who have salaries in the range of 2000$ to 2999$ will get a bonus of 200$. Employees who have salaries in the range of 3000$ to 3499$ will get a bonus of 250$ and so on. Let’s go through the process below.

**Steps:**

- First, we made some modifications as we don’t need the
**Entry Time**and**Joining Date**in this section.

- Now, type the following formula in cell
**D5**.

`=VLOOKUP(C5, INDIRECT("F"&(MATCH($F$5, $F$5:$F$10, 0)+ROW($B$4))&":G10"), 2,TRUE)`

**Formula Breakdown**

Here, we basically use **INDIRECT**, **MATCH **and **ROW Functions **just to define the **lookup range**.

**ROW($B$4) —->**returns- Output:
**{4}**

- Output:
**MATCH($F$5, $F$5:$F$10, 0) —->**becomes- Output:
**1**

- Output:
**MATCH($F$5, $F$5:$F$10, 0)+ROW($B$4) —->**turns into- Output:
**{5}**

- Output:
**“F”&(MATCH($F$5, $F$5:$F$10, 0)+ROW($B$4))&”:G10″ —->**returns- Output:
**F5:G10**

- Output:
**INDIRECT(“F”&(MATCH($F$5, $F$5:$F$10, 0)+ROW($B$4))&”:G10″) —->**becomes**INDIRECT(F5:G10) —->**returns- Output:
**table_array**for**VLOOKUP**(**F5:G10**)

- Output:
**VLOOKUP(C5, INDIRECT(“F”&(MATCH($F$5, $F$5:$F$10, 0)+ROW($B$4))&”:G10″), 2,TRUE) —->**becomes- Output:
**$200.00**

- Output:

Finally, we get the bonus for **Shane **which is 200 bucks.

- Press the
**ENTER**button and you will see the bonus for**Shane**who is mentioned in cell**B5**.

- After that, use the
**Fill Handle**to**AutoFill**the lower cells.

You will see the bonuses for all employees. Thus you can use **VLOOKUP **to find a value that falls between a range.

**5. Implementing VLOOKUP and MATCH Functions to Find a Value That Falls Between a Range**

We can also find time values from a time range using the** VLOOKUP **and **MATCH** functions and thus determine the status of the employees for a day whether they were late or not. Let’s follow the description below.

**Steps:**

- First, we made some modifications as we don’t need the
**Salary**and**Joining Date**in this section.

- Now, type the following formula in cell
**D5**.

`=VLOOKUP(B5,$F$5:$G$7,MATCH($B$6,$B$5:$B$12,0),TRUE)`

Here, the **VLOOKUP **Function looks for the Entry Time in cell **B5** in the range of **F5:G7**. As we want to determine the Status which is in the 2nd column of** F5:G7**, we set 2 as** col_ index_num** with the help of the **MATCH** function. We look for the **Entry Time** in the **Time** column and we want to find them from the range** F5:F7** which will be an **approximate_match**, and for that reason, we set **[Range Lookup]** as **TRUE**. We didn’t want to change the range reference **F5:G7**, so we used an **absolute cell reference**.

- Press the
**ENTER**button and you will see the**Entry Time Status**of**Shane**.

- After that, use the
**Fill Handle**to**AutoFill**the lower cells.

You will see the **statuses **of all employees. Thus you can use **VLOOKUP **to find a value that falls between a **range**.

**6. Applying Nested VLOOKUP to Find a Value That Falls Between a Range**

Another way to find a date value from a date range is to apply the nested** VLOOKUP **function. Suppose the employees who join in a particular date range get bonuses otherwise, they don’t. For instance, if an employee joins between **April** and **June**, he will get a bonus. We can set a formula to use the date range to find an employee whether he gets a bonus or not. Let’s follow the description below.

**Steps:**

- First, we made some modifications as we don’t need the
**Salary**and**Entry Time**in this section.

- Now, type the following formula in cell
**D5**.

`=VLOOKUP(VLOOKUP(B5,$B$5:$C$12,2,FALSE),$F$5:$G$8,2,TRUE)`

Here, the nested** VLOOKUP Function** returns the **cell reference **of **C5 **which refers to the **Joining Date **of **C5**. The main **VLOOKUP Function **then looks for **cell reference **of **C5 **in the **range **of **F5:G8**. As we want to determine the **Bonus Entry **which is in the **2nd column **of **F5:G8**, we set **2 **as **col_index_num**. We look for the **Joining Date **in the **Range **column and we want to find them from the **range** F5:F8 which will be an **approximate_match**, and for that reason, we set **[range_lookup] **as **TRUE**. We don’t want to change the **range reference F5:G8**, so we used an **absolute cell reference**.

- Press the
**ENTER**button and you will see the**Bonus Entry Status**of**Shane**.

- After that, use the
**Fill Handle**to**AutoFill**the lower cells.

You will see the bonus entry statuses of all employees. Thus you can use nested **VLOOKUP** to find a date value that falls between a date range.

## Things to Remember

When you set the range for **Bonus** or **Entry Time** or **Date Range**, you have to put them in ascending order. For example, in the case of setting bonuses according to the **Salary Range**, you should not put $3000.00 before $2000.00 in the **Range** column.

## Practice Section

Here, I’m giving you the dataset that we used to describe the methods in this article so that you can practice on your own.

**Download Practice Workbook**

## Conclusion

In the end, you will understand the fundamental concept of using **VLOOKUP **to find a value that falls between a **range **after reading this article. This is a very powerful function to find particular information about data in an Excel chart. If you have any better ideas or feedback, please share them in the comment box. Your valuable thoughts and feedback will help me enrich my upcoming articles.