WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » VLOOKUP Names

VLOOKUP Names

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

VLOOKUP Range Name

Names are “Named Ranges” for a range of excel cells. We may need to fetch the data from a different worksheet, and for choosing the table array, we need to go to that particular sheet and select the range, so time-consuming and frustrating. Have you ever faced a situation of working with ranges for applying the VLOOKUP formula? The answer is yes, everybody faces tricky situations of selecting ranges for the VLOOKUP function, and often times we may select the wrong range of cells, so it returns wrong results or resultssult. In excel, we have a way of dealing with these kinds of situations, and in this article, we will show you how to use “Names” in VLOOKUP.

Create a Named Range in Excel

Below are examples of names in VLOOKUP.

You can download this VLOOKUP Names Excel Template here – VLOOKUP Names Excel Template
  • Look at the below formula in excel.

VLOOKUP Names Example 1.0

Profit (B3 Cell) is arrived by using formula B1 – B2. If any newcomer comes, they may not understand how profit arrives, i.e., Sales – Cost =Profit.

  • Instead of giving preference to the cell, how about the idea of giving the formula like the below one.

Example 1.1

Yes, these are named ranges in excel. I have named the cell B1 as “Sales” and B2 as “Cost,” so instead of using cell address, I have used the names of these cells to arrive at the profit value.

  • To name the cells, first, select the cell B1 and give your name for this in this name box.

VLOOKUP Names Example 1.2

  • Similarly, do the same for the “Cost” cell as well.

Example 1.3

  • Now we can use these names in the formula.

VLOOKUP Names Example 1.4

As you can see above, we have names of cell addresses instead of the cell address itself, and we can notice the cell address by using the color of the names.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

In the above example, Sales is in the color of Blue. Also, cell B1 has the same color, similarly Cost color is red, and so B2 cell.

How to Use Names in VLOOKUP?

Having learned about names and named ranges, let’s see how we can use these in the VLOOKUP function.

  • Look at the below data set in excel.

Example 2

We have a data table from A1 to D10. It has employee information. On the other hand, we have one more table which has an only employee name in it, so using this employee name, we need to fetch “DOJ, Dept, and Salary” details.

  • Open the VLOOKUP function and choose LOOKUP VALUE as an employee name.

VLOOKUP Names Example 2.1

  • Choose the Table Array as a range of cells from A2 to D10 and make it absolute lock.

Example 2.2

  • Now mention the column number as 2 to for DOJ.

VLOOKUP Names Example 2.3

  • Mention the column number as 3 to for Dept.

Example 2.4

  • Mention the column number as 4 to for Salary.

VLOOKUP Names Example 2.12

So we have results here.

Now using named ranges, we can actually not worry about choosing the range and making it an absolute reference.

  • First, choose the table and name it as “Emp_Table.”

VLOOKUP Names Example 2.5.0

  • Now again, open the VLOOKUP function and choose the lookup value as an F2 cell and make it a column locked reference.

VLOOKUP Names Example 2.6.0

  • Now we need to choose Table Array from A2 to D10 and make it an absolute reference. Instead, we are going to use the name that we had given to this range, i.e., “Emp_Table.”

VLOOKUP Names Example 2.7.0

As you can see above, as soon as we have selected the named range, it has highlighted the referenced range with the same color as the name.

  • Now mention the column index number and range lookup type to get the result.

VLOOKUP Names Example 2.8.0

So, using this named range, we need to worry about selecting the table array every now and then and making it an absolute reference.

VLOOKUP Named Range List and Editing

  • Once the named ranges are created, we can find all the named ranges of the workbook under the FORMULA tab and “Name Manager.”

VLOOKUP Names Example 2.9.0

  • Click on this, and we will see all the named ranges here.

Example 2.10

  • Choose any of the names and click on “Edit” to see its actual cell references.

VLOOKUP Names Example 2.11.0

Look “Emp_Table” named range is referenced from A2 to D10.

Things to Remember Here

  • Named Ranges are useful when regularly apply the VLOOKUP formula, and also it is very helpful if we need to go to different worksheets to select the table array.
  • While naming the range, we cannot include any special characters except underscore (_), space cannot be included, and the name should not start with a numerical value.

Recommended Articles

This has been a guide to VLOOKUP names. Here we discuss how to use the named range in VLOOKUP along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –

  • VBA Userform
  • VLOOKUP with True
  • Power BI VLOOKUP
  • VLOOKUP Tutorial
6 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

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

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

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

WallStreetMojo

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

Login

Forgot Password?

WallStreetMojo

Download VLOOKUP Names Excel Template

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More