Range Lookup with VLOOKUP in Excel (5 Examples)

While applying the VLOOKUP function in Microsoft Excel, we usually use the range lookup feature to extract the approximate or exact match from the data table. In this article, you’ll get to learn how you can use this range lookup feature in the VLOOKUP function with proper illustrations and some common examples.

1. Assigning Letter Grades in Mark Sheet Through Range Lookup with Excel VLOOKUP

In our first example, we’ll determine letter grades for several subjects in a final exam. In the following picture, the table on the right represents the grading system. Based on this grading system, we’ll use the VLOOKUP function to assign a letter grade for each subject in Column D.

The generic formula of the VLOOKUP function is:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Here, the fourth argument ([range_lookup]) of the VLOOKUP function has to be assigned with TRUE (1) or FALSE (0). TRUE or 1 indicates Approximate Match, whereas, FALSE or 0 denotes Exact Match. If you don’t use this optional argument, the function will work out with the default input: TRUE.

To assign letter grades for all subjects based on the grading system table, we have to use TRUE or 1 (Approximate Match) for the lookup range argument. With the approximate match, the function will determine the marks criteria. You cannot go for an exact match here since the random marks will not be perfectly matched with the upper limit of each grade.

📌 Step 1:

➤ Select Cell D5 and type:

➤ Press Enter and you’ll be shown the grade for the first subject- Maths.

📌 Step 2:

➤ Now use Fill Handle to autofill the rest of the cells in Column D.

You’ll be displayed the letter grades for all subjects at once.

Note: You have to keep in mind that the lookup table data must be in ascending order. Otherwise, the approximate match criteria won’t work out.

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

2. Calculating Discount Based on Price Range by Applying Range Lookup with VLOOKUP

You’ll now get to know how you can use the VLOOKUP function with the approximate match as a lookup range to assign a discount based on the total price limit. In the following picture, the table on the left-top is representing some items along with their prices. Another table on the right shows the discount system based on the total price limits.

By using the discount system table, we’ll find out the discount as well as a total discounted price for all items present on the left-top table.

📌 Step 1:

➤ Select Cell C12 to determine the discount and type:

➤ Press Enter and the function will return the discount that has to be assigned to the total price.

📌 Step 2:

➤ To calculate the discounted price in Cell C13, we have to simply multiply the total price with the discount assigned. So, the required formula will be:

➤ Now press Enter and you’ll get the total discounted price at once.

Read More: How to Use VLOOKUP to Find a Value That Falls Between a Range

3. Determining Sales Bonus with the Use of Range Lookup in Excel VLOOKUP

By using a similar method that we have seen previously, we can determine the bonus for the salesmen based on their sales. In the following picture, the table on the right represents the bonus system. In Column D, we’ll directly calculate the bonus based on the bonus percentage and sales value for each salesman.

📌 Step 1:

➤ Select Cell D5 and type:

➤ Press Enter and you’ll find the amount of bonus for Mike based on the number of his sales.

📌 Step 2:

➤ Now use Fill Handle to autofill the rest of the cells in Column D to determine bonuses for all other salesmen right away.

4. Finding Fiscal Quarters from Dates by Using Range Lookup in VLOOKUP

A fiscal quarter is a three-month period on a financial calendar. A financial year has a total of 4 fiscal quarters.

In this section of the article, we’ll determine a fiscal quarter for a date and assign it with Q1, Q2, Q3, or Q4. Assuming that the fiscal year starts from 1 July 2021 in our dataset which will run through the next 12 months. The table on the right in the picture below represents the fiscal quarter system based on the date range.

📌 Step 1:

➤ Select Cell C5 and type:

➤ Press Enter and you’ll be shown the assigned fiscal quarter by the notation.

📌 Step 2:

➤ Autofill the entire Column C to get all other fiscal quarters for the given dates in Column B.

Read More: How to Apply VLOOKUP by Date in Excel

5. Categorizing Data Based on Range Limits with VLOOKUP

In the last four examples, we have applied the VLOOKUP function for a single-limit lookup range. Now in this section, we’ll use the lookup range with both upper and lower limits.

We can apply the method in our first example where we had to calculate the grades for several subjects. In the following picture, the table on the right here is representing minimum and maximum scores or marks for certain letter grades. Based on this grading system, we’ll now assign the letter grades in Column D for all subjects.

📌 Step 1:

➤ Select the output Cell D5 and type:

➤ Press Enter and a letter grade will be assigned to the marks of maths.

📌 Step 2:

➤ Use Fill Handle to autofill the rest of the cells in Column D to get all other letter grades at once.

Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.

Concluding Words

I hope all the examples described in this article will now help you to apply them in your Excel spreadsheets while working with the range lookup feature. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.

Further Readings

• How to Use VLOOKUP If Condition Lies Between Multiple Ranges in Excel
• Lookup and Return Value in a Range in Excel (5 Ways)
• How to Find Second Match with VLOOKUP in Excel (2 Simple Methods)