EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login

VLOOKUP Names

By Ashwani JaiswalAshwani Jaiswal

Home » Excel » Blog » Excel Formula and Functions » VLOOKUP Names

VLookup Names

Vlookup Names (Table of Contents)

  • Vlookup Names
  • How to Use Vlookup Names?

VLOOKUP Names

Vlookup is the most commons function that is used in MS Excel activities. We all have used Vlookup just to look up the values from the source table to the column or cell we want. But we all have faced the problem when we need to select the range of the table. And sometimes, we end up selecting the incorrect table range or column range in the vlookup function. To avoid this, we have the provision of naming the range so that whenever we will be looking up the value from that table or range, we can use the name given to that range. We named this article as Vlookup Names, and we will show you how this works in the below example.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

How to Use VLOOKUP Names?

To create a set up for Vlookup names, we need to first name the table so that only selecting or typing the name of that table will help us select the range. For this, we have some sample data below.

You can download this VLOOKUP Names Excel Template here – VLOOKUP Names Excel Template

vlookup names 1-1

We have two sections, Name and Age respectively. If we place the cursor on any cell, we will only be getting the cell position or vertex in the Name box. Let’s place a cursor at cell B1 first.

vlookup names 1-2

Now to name cell B1, let’s go-to name box and write the name which we want to give to cell B1. Here we have chosen a Name.

vlookup names 1-3

After that, if we again place the cursor at cell B1, we will be able to see the given name.

Examples of VLOOKUP Names

Lets us discuss the examples of Vlookup Names.

Example #1

In this example, we will see the way to name the table used in the Vlookup function. For this, we have sales data as shown below. This table has Owner, Quantity Sold and Product Class columns. And besides that, we have a section where we will be looking up the value from the first table.

vlookup names 2-1

Let’s lookup the value for owner name JOHN in cell F2 and get the quantity sold from the first table using Vlookup as shown below.

Quanity sold

We will then get the value of quantity sold by John as 4159 as per the value from the first table.

Quanity sold 1

Similarly, we will look up the value for Product Class for the same owner John as shown below.

product class

Now, once we enter, we will be able to see the value from the first table to the cell G2, as shown below.

product class 1

Now to name the range for Vlookup, first select the data from cell A2 to C6. And after that, we can name the table range in any name in the Name Box as highlighted below.

product class 2

Let’s change the name to Sales_Info, as shown below.

select cell

Again we will apply the Vlookup in the second table as we did earlier. But this time, we will be choosing the saved table name as the lookup range. So, go to cell F2 and apply vlookup, considering the owner as John again.
When we select the range, we will see it will get converted into the name saved name “Sales_Info”, as we can see below.

vlookup names 2-2

Once we enter, we will get the looked-up value from the first table to cell F2 for owner John.

Quanity sold 2

Do the same thing to get product class as well in cell G2.

vlookup names 2-3

Once we enter, we will see the same value as of product class in cell G2, as shown below.

Popular Course in this category
Sale
Excel Training (21 Courses, 9+ Projects)21 Online Courses | 9 Hands-on Projects | 110+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.9 (11,371 ratings)
Course Price

View Course

Related Courses
Excel Advanced Training (16 Courses, 23+ Projects)Excel Data Analysis Training (16 Courses, 8+ Projects)

product class 3

To test whether we are getting the correct value using Vlookup names, we will change the owner name here. Let’s use the owner name as Sam at cell E2 and see what we get.

After putting Sam in cell E2, we will see the value of Sam’s quantity sold and product class have changed to 8486 and 210, respectively.

Quanity sold 3

Example #2

Once we are done with changing the name of selected ranges, we can then find all the named ranges of the workbook using the below path.

Formula menu -> Name Manager

name manager

Clicking on the Name Manager Option from the Formula tab, we will get the box in which we would have a list of names that we have changed or given to any range.

name manager 1

If we click on the edit button, we will be able to see the Named ranges upon selecting any of the listed named values.

edit name

Pros of Vlookup Names

  • Vlookup names are easy to apply.
  • Vlookup names save a lot of time to in selecting the range.
  • With the help of this, we can avoid any error while selecting the ranges.

Things to Remember

  • It is recommended to use Vlookup Names only when we need to apply vlookup multiple times, or we need to select the huge set of lookup range.
  • When naming the selected range, do not use spaces. Because spaces are not allowed while naming the range. Instead of space, use underscore (”_”)
  • Vlookup names fix the range so that the user does not get any error while executing the Vlookup function for any type of range.
  • Do not consider the header while naming the selected range.
  • We can edit or change the Vlookup names using the methods shown in example-2.

Recommended Articles

This has been a guide to VLOOKUP Names. Here we discuss How to Use VLOOKUP Names along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. VLOOKUP Table Array
  2. VLOOKUP Examples in Excel
  3. VLOOKUP For Text
  4. IF VLOOKUP Formula in Excel

All in One Excel VBA Bundle (120+ Courses, 30+ Projects)

120+ Online Courses

30+ Projects

500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
Excel Functions
  • Excel Formula and Functions
    • VLOOKUP Names
    • Advanced Formulas in Excel
    • Excel Function for Range
    • Excel Template for Payroll
    • Excel Sum Time
    • Payroll in Excel
    • Complex Formulas in Excel
    • Pivot Chart in Excel 2016
    • Advanced Excel Formulas
    • Best Basic Excel Formulas
    • Use Excel As Your Calculator
    • Print Command Button in Excel
    • Excel SUM MAX MIN AVERAGE
    • Excel Basic Functions For Beginners
    • Interactive Excel Dashboard
    • Features of 2016 Excel Workbook
    • What's New In Excel 2016
    • Microsoft Excel Skills
    • Microsoft Excel Shortcuts
    • Microsoft Excel Tips and Tricks
    • Excel Skills
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (34+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (23+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Excel Training
  • Excel Advanced Training
  • Excel Data Analysis Training
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

Download VLOOKUP Names Excel Template

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

डाउनलोड VLOOKUP Names Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

Special Offer - Excel Training Learn More