How to VLOOKUP a Range in Excel

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will learn how to VLOOKUP a range in Excel using different examples. Here, we will use grade marks as a range, discount charts as a range, and employee bonuses as a range for different examples.

The VLOOKUP function is quite important and widely used in Excel. This process is a very useful process. If there is a range and you need to check if the data belongs to that range, then you may use the VLOOKUP function. This process helps to analyze the data and eliminate confusion as well.

Download Practice Workbook

You may download the below worksheet for practice

How to VLOOKUP a Range in Excel: 7 Examples

Here, we will look up a range of criteria for applying multiple criteria. Here we will also show different examples for better understanding.

1. Applying VLOOKUP Function from Letter Grade Range for Marks

  • Initially, select cell D5 and enter the formula to get the grade from the letter grade range.
  • Here, the range is B15:C20.

Vlookup a range of letter grades for marks

2. Adding Discount in a Customer Card Using the VLOOKUP Function

  • Select cell C16 and enter the formula to calculate the discount percentage on a customer card.

Vlookup a range of discount in a customer card

3. Calculating Fiscal Quarters Value Using Date Range

  • In the beginning, select cell D5 and enter the formula to calculate the fiscal quarters.

Vlookup a range of fiscal quarters value

Read More: Vlookup with Time Range in Excel 

4. Using VLOOKUP Function to Calculate Bonus from the Bonus Range

  • Here, select cell D5 to enter the formula and get the bonus according to the experience in the bonus percentage range.

Calculating bonus from bonus range

5. Finding Grades from Data Range Limit Between Two Numbers by Applying VLOOKUP Function

  • Therefore, to find the grades between two numbers, select cell D5 and enter the below formula.

Finding grades from data range limit

6. Applying Named Range to Get Total Prize

  • To create a name range, select the range and write the selected name in the Name Box in Excel (Left from the formula box).

Applying named range to get total prize

  • Now enter the function in cell D5 and complete the process.

Output after applying named range.

7. Getting Grades Using VLOOKUP from Another Sheet

  • Initially, select cell D5 and get the grades from another sheet grade range.
=VLOOKUP(C5,'Grade Number'!$B$5:$C$10,2,1)

Calculating grades from another sheet

Things to Remember

  • You cannot execute this process using the exact match (False/0) in the VLOOKUP function. Always use the approximate match ( True/1) to execute the process.
  • Always lock the lookup range using ($) to secure the range and complete the process in the right way.

Frequently Asked Questions

Q1: Can I use VLOOKUP for a range of values?

Ans: Yes. Lookup range can be a value or a reference cell to a cell as well.

Q2: How do I Vlookup and return multiple values in Excel?

Ans: You can use the Nested VLOOKUP function to vlookup multiple ranges and return multiple values; otherwise, the VLOOKUP function doesn’t return multiple values by default.

Q3: What is range lookup 0 in VLOOKUP?

Ans: You will find the output from the lookup range and use Zero (0) for an exact match. Otherwise, use (1) for an approximate match.


Here, we learned how to vlookup a range using different examples in Excel. Therefore we used grade sheets, bonus charts, and discount amounts as examples and found which numbers belong to which group using the VLOOKUP function. We covered every possible way to complete the process. So, Hopefully, you can solve the problem shown in this article. Please let us know in the comment section if there are any queries or suggestions.

VLOOKUP a Range: Knowledge Hub

<< Go Back to Excel VLOOKUP Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Afrina Nafisa
Afrina Nafisa

Afrina Nafisa Alam, BSc, Industrial and Production Engineering from Ahsanullah University of Science and Technology. She has been working with the Exceldemy project for over 6 months and is currently a web content developer here. She has published over 18 articles and reviewed several during this period. She is keen to learn different features and deliver the knowledge in her current project. She is interested in learning different features of Microsoft Office, especially Excel, Power Query, Data Analysis,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF