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,
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:
In Google Sheets, the Named ranges panel appears:
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,
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:
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.