How to VLOOKUP a Range in Excel – 7 Examples

 

Download Practice Workbook

Download the worksheet.


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)

Vlookup a range of letter grades for marks


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)

Vlookup a range of discount in a customer card


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)

Vlookup a range of fiscal quarters value

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)

Calculating bonus from bonus range


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)

Finding grades from data range limit


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.

Applying named range to get total prize

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

Output after applying named range.


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)

Calculating grades from another sheet


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 ($).

Frequently Asked Questions

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

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

 

 

ExcelDemy
Logo