The Magic of VLOOKUP: G.Sheets, Boss Fights, and Badges

Google Sheets is web based and because of this there are really interesting ways to connect different sheets together using the “=importrange” function.

Capture

Using the =importrange function it is possible to give students individual links to a Google Sheet which then feeds into central Sheet. This is the basis of my Boss Fight Sheets (The Dreadsheets Strike Back: A video guide to the Dreadsheets) but is not really what I wanted to write about in this post.

An interesting way to spice up the visual element of a Boss Fight is to create “Battle Damage”. As Hit Points are reduced it is possible to show this damage on the google sheet. This is one of my 2017-18 Bosses – Tan Kah (my little joke – Tonka) as it takes damage from the students’ attacks.

This display of Battle Damage is the product of VLOOKUP function magic. I was originally introduced to this function as a Digital Badging trick. In order to understand how to get these picture to appear it is necessary to understand some Google Drawing tricks. The following steps will help get you started with your own VLOOKUP image tricks.

STEP 1 = Publishing Your Images in Google Drawing and Drive

The first step in this is to create and publish the desired images. This should be done in Google Drawing. Simply create the Google Drawing.

Screenshot (29)

Add the Image you would like to have VLOOKUP function find in the Google Sheet. Here I have created a Star (well, cut and paste). In order to use this in the Google Sheet you must “Publish” the image.

To do this click “File – Publish to the web”.

Screenshot (30)

It will look something like this.

Screenshot (31)

Click Publish. In a few minutes you will need the link.

Screenshot (32)

STEP 2 = Creating the VLOOKUP Chart

For this post I have created a simple google sheet using VLOOKUP. Once the concept is learned it will be easier to get creative with the function. The VLOOKUP can be done in either a single or two tab sheet process. I am going to show a two tab sheet because it can be hidden from the student view.

First, create a three column chart on Tab 2. Here I have called Tab 2 “Sheet2” which will be important for the VLOOKUP formula. Column A is the Hit Points. The VLOOKUP function will “read” the numbers from this column in order to select which Graphic to display. For example, on this chart if the VLOOKUP number is between 1 and 200 the formula will project the image in cell B3, between 201 and 300 and it will project B4 and so on.

Capture

In Column B, labeled Graphic, the function [=image(_)] has to be added to tell the sheet to display the published picture image in that cell. In Column C paste “Published to the Web” link from the Google Draw step. In this example I am telling cell B2 to display the “Published to the Web” image from cell C2. This continues through the rest of the chart (i.e. B9 has the function =image(C9)).

Capture1

If done correctly the image from the Google Drawing should display in the Graphic column.

Capture2

STEP 3 = Write the VLOOKUP Function in Sheet1

I am not a G.Sheet Function expert but this is how I use the VLOOKUP in order to display Badges or Battle Damage. Most likely there is a more elegant way to accomplish this but my formula can be seen in this example.

Capture3

=if(B5=“”,“”, vlookup(B5,Sheet2!$A$2:B,2))

This starts with an “IF” Function.

This function says IF there is a number in cell B5 THEN procede with the VLOOKUP function. The “,” means “if there is any number”. The VLOOKUP is the last part of the IF function statement and begins after the 2nd comma.

Since there is a number in cell B5, the VLOOKUP function tells the program to look at cell B5 and use that number in the Sheet 2 chart. The formula then goes to tab “Sheet 2” to lookup the number. (The “!” after Sheet2 tells the program that Sheet2 means the Tab with that name in the G.Sheet.) “$A$2:B” tells the function where the chart is located. It starts at Column A cell 2 and ends at the end of Column B. The $s are “anchors” so that if this is cut and paste these values will not change. The “2” at the end of the formula tells the function to fill the cell where the formula resides (in this case Sheet1 cell A1) with the information in the 2nd column. In this case it is the image. (Change this to a 1 and it will display the number in the first column and instead of the image).

If I put 1000 in cell B5 on Sheet1 the vlookup function will go to Sheet2, check column A to find 1000 and then pull the correct image to place. It looks like this.

Capture4

STEP 4 = Creating Battle Damage Images

Go back to the original image and make a copy.

Screenshot (33)

Then alter the image in some way. I like using transparencies to add to the original in some way. Then go back through the publish to the web process.

Screenshot (34)

Then Return to the VLOOKUP chart and add the new “publish to the web” link to column C.

Capture5

Now, if we change the number in Cell B5 it will lookup the new number and if the number is between 900 and 1000 then it will select the image in B11 to display.

Capture6

STEP 5 = Complete the Chart

Compete the VLOOKUP chart on sheet 2 by adding new images. As the B5 cell number changes it will look up the new number in the VLOOKUP chart.

STEP 6 = Get Creative

This is the process I use to create the Battle Damage for my Boss Fights but I am sure that there are other possibilities. For Example, I use a similar system in my gamified gradebook to give student Level Badges. Each student has a personalized gradebook (using the “=importrange” function) and with a VLOOKUP chart. Using the student’s XP count it looks up images that correspond to the various Levels.

This is what the students see. (see the Level Badge in cell C2?)

Capture

The Level Badge is a VLOOKUP function that is hidden from the students in the hidden “LEVEL CHART” tab.

Capture1

All of this VLOOKUP magic can be used in interesting and creative ways. I have been playing with these Google Sheets functions for a couple of years and feel like I am only scratching the surface! I hope that you all create your own VLOOKUP spells and share them with me on TWITTER @MrPowley!

3 Comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s