How to VLOOKUP a Range in Excel – 7 Examples

The VLOOKUP function checks if data belongs to a range.

Example 1 – Applying the VLOOKUP Function to get the Letter Grade in a Marks Range

• Select D5 and enter the formula.
• The range is B15:C20.
`=VLOOKUP(C5,\$B\$15:\$C\$20,2,1)`

Example 2 – Adding a Discount to a Customer Card Using the VLOOKUP Function

• Select C16 and enter the formula.
`=VLOOKUP(C15,\$F\$5:\$G\$10,2,1)`

Example 3 – Calculating the Fiscal Quarters Value Using the Date Range

• Select D5 and enter the formula.
`=VLOOKUP(C5,\$B\$15:\$C\$18,2,1)`

Read More: Vlookup with Time Range in ExcelÂ

Example 4 – Using the VLOOKUP Function to Calculate the Bonus in the Bonus Range

• Select D5 and enter the formula.
`=VLOOKUP(C5,\$B\$15:\$C\$18,2,1)`

Example 5 – Finding Grades in a Data Range Limit Between Two Numbers

• Select D5 and enter the formula.
`=VLOOKUP(C5,\$B\$15:\$D\$20,3,1)`

Example 6 – Applying a Named Range to Get the Total Prize

• To create a name range, select the range and enter the name in the Name Box.

• Select D5 and enter the formula.
`=VLOOKUP(C5,Bonus,2,1)`

Example 7 – Getting Grades from Another Sheet Using the VLOOKUP Function

• Select D5 and enter the formula.
`=VLOOKUP(C5,'Grade Number'!\$B\$5:\$C\$10,2,1)`

Things to Remember

• You cannot use an exact match (False/0) in the VLOOKUP function. Use the approximate match ( True/1).
• Lock the lookup range using (\$).

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

Ans: Yes. The lookup range can be a value or a cell reference.

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

Ans: Use the Nested VLOOKUP function to return multiple values.

VLOOKUP a Range: Knowledge Hub

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

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF