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.


Download Practice Workbook

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


5 Examples of Using Range Lookup in VLOOKUP in Excel

1. Assigning Letter Grades in Mark Sheet with VLOOKUP in Excel

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.

Assigning Letter Grades in Mark Sheet with VLOOKUP in Excel

📌 Step 1:

➤ Select Cell D5 and type:

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

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

Assigning Letter Grades in Mark Sheet with VLOOKUP in Excel

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

Assigning Letter Grades in Mark Sheet with VLOOKUP in Excel

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.


2. Calculating Discount Based on Lookup Price Range in Excel

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.

Calculating Discount Based on Total Price Range in Excel

📌 Step 1:

➤ Select Cell C12 to determine the discount and type:

=VLOOKUP(C11,E5:F9,2,TRUE)

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

Calculating Discount Based on Total Price Range in Excel

📌 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:

=C11-C11*C12

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

Calculating Discount Based on Total Price Range in Excel


3. Determining Sales Bonus with the Use of Range Lookup in 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.

Determining Sales Bonus with the Use of Range Lookup in VLOOKUP

📌 Step 1:

➤ Select Cell D5 and type:

=C5*(VLOOKUP(C5,$F$8:$G$14,2,TRUE))

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

Determining Sales Bonus with the Use of Range Lookup in VLOOKUP

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

Determining Sales Bonus with the Use of Range Lookup in VLOOKUP


4. Finding Fiscal Quarters from Dates by Using Range Lookup

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.

Finding Fiscal Quarters from Dates by Using Range Lookup

📌 Step 1:

➤ Select Cell C5 and type:

=VLOOKUP(B5,$E$8:$F$11,2,TRUE)

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

Finding Fiscal Quarters from Dates by Using Range Lookup

📌 Step 2:

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

Finding Fiscal Quarters from Dates by Using Range Lookup


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.

Categorizing Data Based on Range Limits with VLOOKUP

📌 Step 1:

➤ Select the output Cell D5 and type:

=VLOOKUP(C5,$F$8:$H$14,3,TRUE)

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

Categorizing Data Based on Range Limits with VLOOKUP

📌 Step 2:

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

Categorizing Data Based on Range Limits with VLOOKUP


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.


You May Also Like to Explore

10 Best Practices with VLOOKUP in Excel

VLOOKUP to Return Multiple Values Horizontally in Excel

How to Use VLOOKUP If Cell Contains a Word within Text in Excel

VLOOKUP Partial Match Multiple Values (3 Approaches)

How to Use the VLOOKUP Ascending Order in Excel (3 Ways)

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo