How to Use VLOOKUP to Find a Value That Falls Between a Range

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.

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.

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.

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.

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.

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.

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.

Related Articles

• Vlookup with Time Range in Excel
• 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