Google Sheets VLOOKUP
Table of Contents Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 Music Inventory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 Search Composer Table . . . . . . . . . . . . . . . . . . . . . . 18 Thoughts on VLOOKUP . . . . . . . . . . . . . . . . . . . . . . 30
Digital Maestro Magazine
Page 3
Google Sheets VLOOKUP
Introduction Spreadsheets is an application few teachers think of using in the classroom. Spreadsheets like Excel have been around for a long time. It is a well-established application. Recently, Google Sheets has come into the mix. Sheets is not as established as Excel. It lacks many of the advanced features and functions of Excel. Google Sheets has strengths of its own. It is a solid online application. Excel’s online version can’t compare with the features available in Sheets. The online features open a range of opportunities. All spreadsheets have a core set of functions. Functions are tools used to process tasks and manipulate data. We will be using the VLOOKUP function. The name stands for Vertical Lookup. The vertical refers to the column used to reference information.
Page 4
Digital Maestro Magazine
This function isn’t one known to may users of spreadsheets. It is useful for gathering and organizing data. It is often used to search through lots of information. We will use VLOOKUP to search a small list of information for a Fine Arts department. To understand VLOOKUP we need to understand some spreadsheet basics. Spreadsheets are a collection of sheets. These sheets form a workbook. An Excel file or a Sheets file is a workbook that contains several sheets. The information in the sheets is usually kept separate. The workbook we
Google Sheets VLOOKUP will use has sheets with lists. These lists contain inventory items for musical scores. The sheets are used to categorize the sheets by their genre. Sheets are a series of rectangles organized Into columns and rows. The columns are labeled with letters from A to Z. Rows are numbered beginning at 1 and going on into the thousands. The cells are referred to by the letters and numbers where they intersect. The first cell is referred to as cell A1. This naming of cells and references is very important. We use these cell references in VLOOKUP. The information in cells is typically labeled. We assign these labeled based on the information. The labeled are typically placed at the top of each column. The labels are typically called headings. One piece of information is typically placed below each heading.
Digital Maestro Magazine
Page 5
Google Sheets VLOOKUP
Music Inventory This is an example of a project I actually worked on for the Fine Arts department. They needed a way to search through their database of musical scores and music sheets. Teachers often need access to music sheets and scores for performances. They call the Fine Arts department and ask for a piece of music. They usually had to wait for one person that had the file. This person would open the file and search for what the teacher needed. This often required the teacher to wait a day or more to get a response. They wanted a way to streamline the process. They wanted a way for more people to have access. They also needed a way for those with access not to alter the inventory. They needed a way for the people to search the inventory. Most of them didn’t know how to use a spreadsheet.
The music in each sheet has headings. We don’t need the headings for VLOOKUP but it helps. We will develop a search for when teachers call and ask for a title. They might also ask for titles from a specific composer. We will create a search option for composer too.
They had all the information in a spreadsheet. They used Microsoft Excel. I took the spreadsheet and uploaded it to Google Drive. The spreadsheet had several tabs. Each tab represents a set of musical sheets. These music sheets include Ensemble, Jazz, Orchestra, and Mariachi. They had done most of the work for me. Yay! Use this sheet to follow along. The link is at http://bit.ly/musicDBVlookup.
Page 6
Digital Maestro Magazine
It’s important to keep the data separate from the search. This avoids confusion and prevents anyone from changing the inventory. This is one of the requests from the Fine Arts department. Check!
Google Sheets VLOOKUP Click the Plus button near the lower left corner of Google Sheets. This will create a new sheet.
Title the sheet, Music Search.
Go to cell A1 and type “Search Ensemble Scores”. Don’t include the quotation marks. The newly created sheet is placed after the Ensemble sheet.
This sheet will be first. Click and drag the sheet to the left of the Ensemble sheet.
The user will use the first cell below the title to search for a music score. We need to make this cell longer. Move your mouse cursor between the letters A and B. The mouse arrow will change to an arrow pointing to the right. Click and drag to the right.
Click the triangle next to the Sheet name. Select the option to rename the sheet.
Digital Maestro Magazine
Page 7
Google Sheets VLOOKUP I moved the column width enough to give me extra room. Look at the image if you are curious as to how wide I made the column. I moved it to the position right of B column.
Click on cell B2. This is where we will import the first part of the score information. Type “=vlookup” inside the cell. Don’t include the quotation marks. Google provides an information box. This box confirms we are using the right function. It also provides useful information for using VLOOKUP. Click once on the message box.
The requirements are a search key, range, index, and sort. We will go through each option.
The search key is what we use to search for the information. That search is what the user will type in the cell on the left. This is cell A2 in our example. The range is where the information is located to search. That is the Ensemble sheet. The index is what information we want to retrieve from the list. The sort option is used to inform VLOOKUP if the information in the range is already sorted. It is better to have the information sorted when using VLOOKUP. Type A2 followed by a comma. We just instructed VLOOKUP to look to the contents of cell A2. It will use this information to find what we need. That is easy. The Range is a little trickier.
The message box begins with the requirements for the function. These requirements are called arguments in a function.
Page 8
Digital Maestro Magazine
Google Sheets VLOOKUP VLOOKUP already knows we are going to enter a range. Click the Ensemble tab. The Ensemble sheet opens. The cell with our function is hovering over the sheet. This is here to help us get visual feedback on our selection.
Click on cell A2 in the Ensemble sheet. The function updates. The Range includes the Ensemble sheet name followed by A2. This is the first part of our Range. We don’t need the headings so they are not selected.
Scroll to the bottom of the list. Look for the last column.
Press and hold the shift key. Click the cell with Woodwind. This combination of holding the shift key and clicking selects everything between cell A2 and the cell we just clicked.
The Range updates to include the last cell we clicked. Let’s take a closer look at the Range. The exclamation after the sheet name identifies this as a sheet name to VLOOKUP. The colon between the first cell and the last cell identifies the range. In this example, the Range is A2 to E578.
Type a comma after the Range. The next parameter we need is the index. This is the information we want to be returned.
Digital Maestro Magazine
Page 9
Digital Maestro Magazine Thank you for reading these sample pages.
Purchase the full issue at‌
digitalmaestro.org/shop
Microsoft Class Notebook for OneNote
Digital Maestro Magazine
35