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(C5,$B$15:$C$20,2,1)
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(C15,$F$5:$G$10,2,1)
3. Calculating Fiscal Quarters Value Using Date Range
- In the beginning, select cell D5 and enter the formula to calculate the fiscal quarters.
=VLOOKUP(C5,$B$15:$C$18,2,1)
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.
=VLOOKUP(C5,$B$15:$C$18,2,1)
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.
=VLOOKUP(C5,$B$15:$D$20,3,1)
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).
- Now enter the function in cell D5 and complete the process.
=VLOOKUP(C5,Bonus,2,1)
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)
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.
Conclusion
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
Get FREE Advanced Excel Exercises with Solutions!