Vlookup Names (Table of Contents)
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 table. And sometimes we end up selecting the incorrect table range or column range in vlookup function. To avoid this, we have 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 below example.
How to Use VLOOKUP Names?
To create a set up for Vlookup names, need to first name the table in such a way that only selecting or typing the name of that table will help us to select the range. For this, we have some sample data below.
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 Name box. Let’s place a cursor at cell B1 first.
Now to name the cell B1, let’s go to name box and write the name which we want to give to cell B1. Here we have chosen Name.
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 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.
Let’s lookup the value for owner name JOHN in cell F2 and get the quantity sold from first table using Vlookup as shown below.
We will then get the value of quantity sold by John as 4159 as per the value from the first table.
Similarly, we will look up the value for Product Class for the same owner John as shown below.
Now, once we enter, we will be able to see the value from the first table to the cell G2 as shown below.
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 Name Box as highlighted below.
Let’s change the name as Sales_Info as shown below.
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 lookup range. So, go to the cell F2 and apply vlookup considering 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.
Once we enter, we will get the looked up value from first table to cell F2 for owner John.
Do the same thing to get product class as well in cell G2.
Once we enter, we will see the same value as of product class in cell G2 as shown below.
4.9 (7,127 ratings)
View Course
To test, whether we are getting the correct value using Vlookup names, we will change the owner name here. Let’s use 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.
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 below path.
Formula menu -> Name Manager
Clicking on Name Manager Option from Formula tab, we will get the box which we would have list of names that we have changed or given to any range.
If we click on edit button, we will be able to see the Named ranges upon selecting the any of the listed named values.
Pros of Vlookup Names
- Vlookup names is easy to apply.
- Vlookup names saves a lot time to in selecting the range.
- With the help of this, we can avoid many 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 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 fixes the range so that 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 –
