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.

vlookup to find a value that falls between a range


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)

vlookup to find a value that falls between a range

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.

vlookup to find a value that falls between a range

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)

vlookup to find a value that falls between a range

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.

vlookup to find a value that falls between a range

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)

vlookup to find a value that falls between a range

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.

vlookup to find a value that falls between a range

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)

vlookup to find a value that falls between a range

Formula Breakdown

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

  • ROW($B$4) —-> returns
    • Output: {4}
  • MATCH($F$5, $F$5:$F$10, 0) —-> becomes
    • Output: 1
  • MATCH($F$5, $F$5:$F$10, 0)+ROW($B$4) —-> turns into
    • Output: {5}
  • “F”&(MATCH($F$5, $F$5:$F$10, 0)+ROW($B$4))&”:G10″ —-> returns
    • Output: F5:G10
  • 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)
  • VLOOKUP(C5, INDIRECT(“F”&(MATCH($F$5, $F$5:$F$10, 0)+ROW($B$4))&”:G10″), 2,TRUE) —-> becomes
    • Output: $200.00

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.

vlookup to find a value that falls between a range

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)

vlookup to find a value that falls between a range

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.

vlookup to find a value that falls between a range

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)

vlookup to find a value that falls between a range

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.

vlookup to find a value that falls between a range

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.


Related Articles

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo