More VLOOKUP Magic: A Half-Baked Random Generation G.Sheet Trick

I recently wrote about the MAGIC of the VLOOKUP function in Google Sheets. This post explained how I use the function in order to create Battle Damage in my Boss Fights as well as create simple Level Badges based on XP scores in my gamified grade book. I realized that I have been playing with a mashup of the VLOOKUP and the Random Number Generator function RANDBETWEEN. You can create random conditions and events with VLOOKUP.

Next year my theme will be based on a Super Hero storyline and I was tinkering with providing students with a random name generator. My district has banned most of the websites that would allow for random names and character creation. While I haven’t completed my version I have been toying with the following Google Sheets. It also show the VLOOKUP formula that is on a single tab.

In this example the VLOOKUP table is in columns A through C. Column A is a number 1-66, Column B are Adjectives, and Column C are Nouns.

Capture

In Cell D2 and D3 I have used the random number generator function RANDBETWEEN. This will randomly generate a number between 1 and 66 [=randbetween(1,66)].

Capture1

In the example below you can see the mashing of the Random Number with the VLOOKUP function.

The VLOOKUP function is taking the randomly generated number in Cell D2 and using it to lookup the number in column A. The formula will then return the word in column B because I have included the number 2 after the second comma which means “look in the second table column”.

Capture2

Here you can see how to access the third column in the table by changing the 2 to a 3.

Capture4

Every time the student refreshes the page a new random number will be created in cells D2 and D3. This in turn means that a new combination of words will be returned from the VLOOKUP function will be returned. The could keep refreshing the page until the get a combination they like. So if they don’t like The Dashing Beast they could refresh and get the Wacky Fox or The Night Axeman.

This slideshow requires JavaScript.

I am thinking about adding a third column with a randomized image that would act as the student’s Super Hero Symbol.

Naturally this would need a lot of spit and polish to make it look nice before I would use this in class but the concept is interesting. I could see this being used to create random events during a quest or random challenges during a Boss Fight. The RANDBETWEEN updates every time a new piece of data is added into the spreadsheet. After each attack the Boss could randomly attack a group or a random obstacle could be programmed. This is certainly still in the Half-Baked stage!

1 Comment

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 )

w

Connecting to %s