Using a named range with the VLOOKUP function

As is often the case when creating VLOOKUP functions, we will want to use absolute references when referring to the list of sales reps. We will use a named range as an absolute reference.

NOTE: Named ranges are introduced in the following pages: Spreadsheets the Basics: Utilizing a named range (Microsoft Excel) and Spreadsheets the Basics: Utilizing a named range (Google Sheets).

Acquiring the practice workbook

If you are beginning this course at this point, you will need to access the practice workbook.

Downloading the practice workbook for Microsoft Excel

If working in Microsoft Excel, you will download and extract the workbook.

To begin downloading the practice workbook, click the following button:

Exercise Files

For detailed instructions on downloading and extracting files in Google Chrome or Firefox, visit Getting Started with Exercise Files.

Copying the practice workbook in Google Sheets

In Google Sheets, you will copy the workbook to your Google Drive.

To begin copying the practice workbook in a new browser window,

Right-click Copy practice workbook, Click Open link in new window

The file begins to open in a new window of the web browser. Before the document will open and can be edited, we need to make a copy of it.

Move to the new browser window.
To copy the workbook file, in the browser window,

Click

A copy of the file is now open and, by default, is saved in your Google Drive. The filename now has the words "Copy of" at the beginning.

Using a named range as an absolute reference

We will now work in the Sales Reps worksheet. This example shows a list of stores with addresses. We will use the VLOOKUP function to find the sales rep for each store. The sales representatives are determined by state and are listed in the data in cells G5:J24.

To move to the Sales Reps worksheet,
Click the Sales Reps tab

Named ranges are a form of an absolute reference. By naming a range and using that name in a formula or function, we ensure that the reference will not adjust as the formula or function is copied.

The syntax of the VLOOKUP function requires the data being looked for to be in the first column of the lookup range. Because of this, the lookup range will be H6:J24 and will not include column G. Before we create the function, we will name this range.

The process of naming a range is different in Microsoft Excel and Google Sheets, but the end result is the same.

To select the range H6:J24,
Press & drag from H6 to J24
To begin naming the range,
Right-click the selected range
To open the name defining options, in the right-click menu,

In Microsoft Excel, Click Define Name...
In Google Sheets, Click Define named range

In Microsoft Excel, the New Name dialog box opens:

New Name dialog box in Microsoft Excel

In Google Sheets, the Named ranges panel appears:

image of the named ranges panel in Google Sheets

Both of these tools give us an option to name the range and confirm that the range to be named is in the Sales Reps worksheet, cells H6:J24.

Let's name the range.

To name the range in Microsoft Excel,
Double-click in the Name field, type: Rep_State Enter key
To name the range in Google Sheets,
Double-click to select the placeholder name, type: Rep_State Enter key

To close the Named ranges panel, in the upper right corner of the panel,
Click

The range has been named. We can now use it in the VLOOKUP function.

We want to place the VLOOKUP function in column E. The function will look for the state (column D) in the range Reps_State. We want the sales reps' names to be returned to column E. Those names are in the third column of the lookup range. Finally, we want an exact match, so we will use the false argument. Our function will look like this:

=VLOOKUP(D2,Rep_State,3,false)

To make cell E2 the active cell,
Click in cell E2
To put the VLOOKUP function in cell E2, on the keyboard, type:
=VLOOKUP(D2,Rep_State,3,false) Enter key

To copy the function to the remaining cells,
Click cell E2, Double-click the AutoFill handle

The names of the sales representatives are now in column E.

Using a named range with the VLOOKUP function

Acquiring the practice workbook

Downloading the practice workbook for Microsoft Excel

In Microsoft Excel, download and extract the practice workbook.

Copying the practice workbook in Google Sheets

In Google Sheets, copy the practice workbook.

Using a named range as an absolute reference

Step 1. Move to the Sales Reps worksheet.

Step 2. Begin naming the range H6:J24.

Step 3. Name the range.

Step 4. If working in Google Sheets, Close the Named ranges panel.

Step 5. Put the VLOOKUP function in cell E2.

Step 6. Copy the function to the remaining cells in column E.