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 VLOOKUP formula? The answer is yes, everybody faces tricky situations of selecting ranges for VLOOKUP function and often times we may select the wrong range of cells, so it returns wrong results or error result. 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.
- Look at the below formula in excel.
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 reference to the cell how about the idea of giving the formula like the below one.
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.
- Similarly, do the same for the “Cost” cell as well.
- Now we can use these names in the formula.
As you can see above we have names of cell address instead of cell address itself and we can notice the cell address by using the color of the names.
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 VLOOKUP function.
- Look at the below data set in excel.
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.
- Choose the Table Array as a range of cells from A2 to D10 and make it absolute lock.
- Now mention the column number as 2 to for DOJ.
- Mention the column number as 3 to for Dept.
- Mention the column number as 4 to for Salary.
So we have results here.
Now using named ranges we can actually not worry about choosing the range and making it as an absolute reference.
- First, choose the table and name it as “Emp_Table”.
- Now again open the VLOOKUP function and choose the lookup value as F2 cell and make it a column locked reference.
- Now we need to choose Table Array from A2 to D10 and make it as an absolute reference, instead, we are going to use the name that we had given to this range i.e. “Emp_Table”.
As you can see above as soon as we have selected the named range it has highlighted the referenced range with the same color of the name.
- Now mention the column index number and range lookup type to get the result.
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”.
- Click on this and we will see all the named range here.
- Choose any of the names and click on “Edit” to see its actual cell references.
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 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 downloadable excel template. You may learn more about excel from the following articles –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
