VLOOKUP Names

What Is VLOOKUP Using Names?

VLOOKUP Names helps us lookup for a value from a particular named range. If you are familiar with the VLOOKUP function in Excel, you must know that we must select the table array to specify the VLOOKUP function to look for the desired value in that table range.

Sometimes when we work with large amounts of data, It gets frustrating to select a large data range. If the data range is in different worksheets, then switching between the worksheets to select the data range is time-consuming. This is where the concept of ‘VLOOKUP Names’ or ‘Named Ranges’ comes in handy.

Irrespective of which worksheet we are in, if the data range is a named range, then we can use that name to refer to that data table with ease. So, users don’t have to toggle between worksheets or scroll through a large data range to choose the table array.

What Is Named Range In Excel?

Let us first understand the concept of Name range in Excel.

For instance, look at the following simple data in Excel.

In cell B4, we have applied the formula to calculate the ‘Total Profit’ by using cells B2 (Total Sales) and B3 (Total Cost).

This is the normal procedure of applying formulas. However, by just looking at the formula B2-B3, some users may not get the actual idea of the calculation. Instead, we will use named cells to calculate ‘Total Profit’.

Step 1: Select cell B2, and in the name, the box gives the name ‘Sales’ as shown in the following image.

Now, cell B2 is given the name ‘Sales’.

Step 2: Whenever we want to reference cell B2, we can use the name ‘Sales’ by just typing it.

Similarly, select cell B3 and give a name as ‘Cost’.

Now, cell B3 is referenced by using the name ‘Cost’.

Step 3: In cell B4, enter an equal sign and type the word ‘Sales’.

Step 4: Enter the formula by using named range cells.

As we can see, the ‘Sales’ cell is highlighted in blue color, and the ‘Cost’ cell is highlighted in red color.

In the formula bar also, we will not see the cell address rather, we will see only the names that we have given for those cells.

It doesn’t matter which worksheet we are in. If we want to reference cell B2, we can use the name ‘Sales’; similarly, if we want to use cell B3, we can use the name ‘Cost’ within the same workbook.

Like this, we can create named ranges and use them to reference cells or range of cells.

How To Use VLOOKUP With Named Range In Excel?

If you are new to using VLOOKUP function, let us recollect the syntax of the VLOOKUP function first.

The VLOOKUP syntax comprises 4 parameters, where 3 are mandatory, and the remaining one is an optional parameter.

• Lookup_value: The value for which we are trying to retrieve the result from the table array (2^nd argument). It is a mandatory argument.
• Table_array: This will be either a range or table array where we search for the lookup value. It is also a mandatory argument.
• Col_index_num: From the given table array from which column, we are looking for the result.
• [Range_lookup]: In this argument, we need to specify the kind of match we need to do:
  • 0 or FALSE – This will search for the exact match of the lookup value in the table array. If nothing is specified, 1 or TRUE will be the default mode.
  • 1 or TRUE – This will search for the approximate match of the lookup value in the table array.

Let us have a look at the example of using names in the VLOOKUP function. We have the following sales data in Excel.

From the above table, we will try to find the following two product IDs information.

Examples

Example #1 – Create A Named Range To Get The Range Reference From Another Worksheet

Named Ranges will come in handy when we try to retrieve the value from one worksheet to another. Let us look at one such example here.

We have the following data in the worksheet called “Employee_Master”.

Now we have some employee data in another worksheet called “Salary”.

Now we need to fetch salary information for the above 3 employees in the worksheet “Salary” from the “Employee_Master” worksheet.

• Step 1: Enter the VLOOKUP function in cell B2 of the “Salary” worksheet.

• Step 2: Choose the lookup value as cell A2.

• Step 3: Now, we need to choose the table from which we are trying to retrieve the salary information. To choose the table array, we need to switch to the worksheet “Employee_Master” and then choose the range of cells from A2:E10.

However, this switching of worksheets is not a good idea. To work efficiently and super-fast, we need to use named ranges.

• Step 4: Exit the VLOOKUP mode. Choose the range of cells from A2:E10 in the “Employee_Master” worksheet and give a name as “Employee_List”.

• Step 5: Now, in the “Salary” worksheet, enter the VLOOKUP function and give the lookup value as cell A2.

• Step 6: Now, to choose the table array, we need not have to go to the “Employee_Master” worksheet; rather, just type the name that we have given in the previous step.

• Step 7: Enter the column index number as 5.

• Step 8: Enter the range lookup as “False” or 0 to get the exact match.

There we go. We have retrieved the salary information from the worksheet “Employee_Master” without getting the reference from that sheet.

Example #2 – Create Dynamic Named Range To Avoid Mistakes

Continuing from Example #1, we will elaborate on the need for dynamic ranges in Excel. For instance, let us add one more employee to the “Employee_Master” worksheet.

We have added a new employee in row number 11.

Let us add the same employee ID in the salary sheet as well to retrieve the salary details.

Using the same formula, we are trying to retrieve the salary information. However, we ended up getting the #N/A error.

The reason for this is the existing named range “Employee_List” referenced to the range of cells from A2:H10 only. Anything added after this range will not be a part of the named range, so the current named range fails to take the new additional data dynamically.

Let us make the existing name range “Employee_List” dynamic by using the following steps.

• Step 1: Go to the Formula tab and click on the Name Manager in excel.

• Step 2: The Name Manager window opens up. Select the name “Employee_List” and then click on the Edit button.

• Step 3: The Edit Name window opens up. In the Refers to box, enter the following formula.

=OFFSET(Employee_Master!$A$2,0,0,COUNTA(Employee_Master!$A$2:$A$1048576), COUNTA(Employee_Master!$2:$2))

• Step 4: Click OK, and the dynamic named range is ready.

Now, we can see the result for the newly added employee ID in the worksheet “Salary”.

Formula Explanation: The formula used to make the named range dynamic is below:

=OFFSET(Employee_Master!$A$2,0,0,COUNTA(Employee_Master!$A$2:$A$1048576), COUNTA(Employee_Master!$2:$2))

=OFFSET(Employee_Master!$A$2,0,0, = OFFSET Excel function starts from cell A2; it moves down zero rows and moves right zero columns.

COUNTA(Employee_Master!$A$2:$A$1048576) = COUNTA Excel function will return how many rows are filled up in column A, and the OFFSET function will offset those many rows.

COUNTA(Employee_Master!$2:$2) = COUNTA function will return how many columns are filled up in row number 1, and the OFFSET function will offset those many columns.

Like this, we can create a dynamic named range to make life easy and work efficiently with the VLOOKUP function.

Important Things To Note

• The named Range name should be unique, and there should not be any duplicates.
• The name should not contain any space characters or special characters except underscore (_), and the name should not start with a numerical value.
• The named range scope should be “Workbook” to use the name anywhere within the workbook.
• The VLOOKUP function takes does not require the absolute reference in excel because the named range itself is an absolute reference.
• No need to include the table header while naming the range.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand the VLOOKUP Names, its formula, uses and examples. You can download the template here to use it instantly.

Recommended Articles

A guide to VLOOKUP Names. Here we learn to use the Excel VLOOKUP using names, along with step-by-step examples & downloadable excel template. You can learn more from the following articles –

• VLOOKUP From Another Sheet
• VLOOKUP With Multiple Criteria
• VLOOKUP with IF Statement