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

**Table of Contents**hide

## Download Practice Workbook

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

**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 **which 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 don’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**.

**Read More: How to Use Column Index Number Effectively in Excel VLOOKUP Function**

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

We can also use **the VLOOKUP Function **to find **time **value 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 don’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 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 **VLOOKUP **to find a **date **value that falls between a **date range**.

**Read More: How to Apply VLOOKUP by Date in Excel**

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

**Read More: How to Use VLOOKUP If Condition Lies Between Multiple Ranges in Excel**

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

We can also find **time **value 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 don’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**.

**Read More: Range Lookup with VLOOKUP in Excel (5 Examples)**

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

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