When it comes to Google Sheets I am an Enthuiastic Amatuer but I have been hacking away at spreadsheets long enough to pick up a few tricks and one of my favorite is the “VLookup”.
VLookup stands for Vertical Lookup and it is the function that allows the user to create a table of data, then search that table, and finally return a piece of data based on the search term. I VLookup to create automated Letter Grades, automate Badge Images, and add Battle Damage in my Dreadsheet Boss Fight system. It is a fairly straight foward function that can be creatively applied which is why I use it so often!
The Function Syntax
=VLOOKUP(search_key, range, index, [is_sorted])
The Function Breakdown
This function begins with the creation of a data table on the spreadsheet. This image is a screencap of a very simple number to letter grade conversion table. The Data Table is in Columns K and L Rows 2 through 7.
In the syntax “seach_key” is the value that will be searched for within the table. This can be a specific number OR you could refer to a cell and it will look at the value in that cell. For example, in the image below, The Highlighted Cell H2 contains the VLOOKUP function. The “search_key” in the function is G2 so the VLOOKUP formula is going to read the value in cell G2 (in this case 92) and then find that value in the table.
The next part of the formula is “range” which identifies the beginning (top left most cell) and end (bottom right most cell) of the data table. In this simple VLOOKUP the range is K2:L7.
The “index” will tell the formula which column‘s value should be returned. For example, in the K2:L7 table above, if the index is set to “2” then when the number value in K is looked up the value in the second column from the right will be returned into the formula. If there is a bigger table in the Range, for example K2:M7 then a range of 3 would lead to the third column from the right’s value being returned.
I don’t really understand the “[is_sorted]” part of the syntax but it has something to do with the true/false nature of the lookup. For the basic vlookup with an organized numeric value like we would find in a gradebook or a battle damage function leaving this part of the formula blank will not change the results.
Cross Tab Table Lookup
It is possible to have the VLookup Function in one tab and read off of a second tab. This is my Dreadsheet Boss Page. In the formula bar you can see an “If-Then” statement first but for this function look at the – vlookup(B17,Damage!$A$2:BB,2)). B17 is the cell that search_key which means that the number in that cell will be the number looked for on the Data Table. The Damage! part of the formula is telling the function to look for the table on the Damage Tab with the “!” telling the function that Damage is the name of a tab on the Google Sheet. The Data Table starts at A2 and BB (meaning the lowest cell of column BB) is the end of the table meaning the data table is huge. The 2 at the end is telling the program to return the value in the 2 column on the table which in this case is column B.
What’s with the $ signs?
In a function the $ signs of $A$2 tell Google Sheets not to change those numbers/letters if I were to copy or drag the function into another cell.
VLOOKUP Ideas to Explore
Create a Simple Gradebook Lookup
Create a Badge System