Using VLOOKUP to Generate Game Content in Excel

Part 1 on Monday looked at using RANDBETWEEN and IF statements to generate random numbers, evaluate conditions, and return a result.  Today we'll take it another step of the way in terms of generating stuff for your game by showing how the VLOOKUP function in Excel works.  VLOOKUP stands for Vertical Lookup, and requires your content to be in a vertical list.  The first column is the lookup value -  the number you’d be trying to roll on a die in a table.  If your table consists of 10 wandering monsters, you'd expect column one to list the numbers 1 to 10, in ascending order.  The whole set of columns (B2 through E11) is an array.  Here's a simple table we'll use for an example:

The basic syntax of the function is =VLOOKUP(lookup value, array, column index of the data to retrieve).  In the example above, column B is index 1, C is 2, D is 3, E is 4.  Here's how a simple VLOOKUP formula would look to return a random 1st level monster from that table:

=VLOOKUP(RANDBETWEEN(1,10),B2:E11,2)

Let's break it down.  A random number (RANDBETWEEN(1,10) is substituted for the lookup value.  The array is the entire table - cells B2 through E11.  I also recommend going and putting dollar signs in front of the table array values so the actual formula looks like this:

  =VLOOKUP(RANDBETWEEN(1,10),$B$2:$E$11,2).

The dollar signs lock the lookup (in Excel terms, it's called an absolute reference).  Normally a formula increments the cell values when you copy and paste the formula to other cells.  The dollar signs ensure the vlookup consistently checks the correct cells for values.

You can easily change which column the content is retrieved from by updating the column index -

=VLOOKUP(RANDBETWEEN(1,10),$B$2:$E$11,3)

By updating the column index to 3, a result of 7 on the RANDBETWEEN number yields "gray ooze".  You can even get fancy and make the column index a random number, too - let's say you didn't care if the VLOOKUP returned a 1st, 2nd, or 3rd level monster, you could put in a formula like this in place:

=VLOOKUP(RANDBETWEEN(1,10),$B$2:$E$11,RANDBETWEEN(2,4))

VLOOKUP is an important building block.  Along with the RANDBETWEEN and IF statements we looked at Monday, we're cooking with gas!  Let's look at a simple practical example I used in the Black City game to generate tons of Viking names on the fly.  Consider this logic I used for Viking names.  First you have basic names (given names).  A last name has a 50% chance of either being a father's surname, a location, or a nickname.  So Vigor son of Baldi could be Vigor Baldison, Vigor of Zealand, or Vigor the Hook Nose.  As long as you have content tables with the three lists - the first names, the locations, and the nicknames, automating name creation in Excel with the functions we've reviewed is a snap.

In this next example, here are 10 given names (out of around 300) to show how the concept works.  I called this worksheet "name content":

On the next worksheet, I'll go ahead and put the following formulas in place.  You'll note that when you highlight an array of cells in an external worksheet, Excel prepends the sheet name to the array - it shows up as 'name content'! in the examples below to let you know it's referencing another sheet in the workbook.

Column 1 - generate first name
Formula:  =VLOOKUP(RANDBETWEEN(1,10),'name content'!$A$2:$B$11,2)

Column 2 - use a random number to generate type of last name (1-4).  This is just a working cell to create a value for columns 3 through 5 to check against:
Formula:  =RANDBETWEEN(1,4)

Column 3 - check the value in column 2 to see if there's a surname (on a 1 or 2 out of 4); if so, retrieve a firstname from the 'name content' sheet, and append "son" to the end to make a Viking surname.
Formula:  =IF(B2<3,VLOOKUP(RANDBETWEEN(1,10),'name content'!$A$2:$B$11,2)&"son","")

Column 4 - check if the last name identifies the character's homeland; if so, retrieve a location and add "of " to the beginning:
Formula:  =IF(B2=3," of"&VLOOKUP(RANDBETWEEN(1,10),'name content'!$A$2:$C$11,3),"")

Column 5 - check if there's a nickname; if so, retrieve a nickname and add "the " to the beginning of the nickname:
Formula:  =IF(B2=4,"the "&VLOOKUP(RANDBETWEEN(1,10),'name content'!$A$2:$D$11,4),"")

Column 6 - Let's concatenate the first name and the content of the other columns together to make a handy column for use in the game:
Formula:  =A2&" "&C2&D2&E2

Here's the working table with all the formulas in action:

We get a nice list of Viking names (all with A's in the first name, since I only put about 10 in the content table.  But you get the idea).  Here's that list:

Aran Anirson
Agnar of Dublin
Agnar the Black
Alrek the Beer-Drinker
Anir the Bellower
Annund Armodson
Alfgeir Agnarson

Muhaha - what will you do with your new powers of Excel?  Many times, I generate a list of names and personalities in advance, and keep them saved in a text file for use during the game.  When a need arises, I'll just pull the top one off the list as necessary during the game.  When the players run into bandits, or traders, or start talking to a random guy at the tavern, the improvisation goes much better when I can rattle off that it's "Alrek the Beer-Drinker" who they're talking to instead of 'random Viking guy'.

In fact, I use long lists of personalities, quirks, and mannerisms generated through VLOOKUPS quite a bit. I don't do well improvising interesting people on the fly and can use a little help. I may not know how I want to portray the bandit leader (so they're not like every other bandit) when I roll a wandering monster, but once I see that the leader is violent, or jealous, or risk averse, I can take it from there pretty easily and make a fun encounter.  NPCs with personalities keep the game interesting for me, and you never know when that random encounter is going to lead to a memorable scene or a future henchman or an enduring villain because you took the time to give the character a name and a personality hook, and then something funny or meaningful happened.

Getting back to Dungeon Content, I try to have giant tables of empty room types and trap types to integrate into the random stocker, as well as monster customized for each level and area of the dungeon.  You should see how the same techniques of content lookups and concatenation used to generate the Viking names can be used the same way to integrate content for a dungeon stocker.  However, let's be clear about something.  This is not programming.  The goal is not to make a finished tool to publish.  The goal is to give you a reason to dump all those random tables and content lists you've been acquiring, to dump them in Excel and to arm you with some basic techniques to generate tons of cool content during your game prep - and to do it much quicker than rolling and flipping pages and writing stuff down.

However, one type of content where there's no substitute for hard work and brainstorming is 'Specials'.  The 1E DMG has a pretty good list of dungeon objects and special properties, and -C over at Hack and Slash put together a list of types of specials - descriptors like Riddles, Quests, Terraformers, and Shops.  For a large dungeon like Taenarum, I try to smash it all together and see if something inspires me while trying to make sense of a bunch of random inputs.

Here's an example from this morning:  I was working on a room on level 4 of Taenarum and it contained a 'Special'.  The random results indicated it was trapped, weird, involved a pillar or column, and was intelligent.  Furthermore, the trap had to do with blindness.  Yikes.  What to do?  Well, the adjacent room has 'tritons' which clambered up from level 5 below.  Tritons in the game are scaly fish men with bug eyes and gills.  I decided the thing in the room is an Obelisk (close enough to a pillar for my purposes) and the tritons are worshipping it.  They're triton heretics who have turned their back on Poseidon, and the Obelisk represents Dagon, who they consider a scaly lord of the deep.  Furthermore, Dagon is trapped in the Obelisk itself!  In truth, Dagon is no god or demon, but was an Atlantean sorcerer imprisoned in the rock by Poseidon before the sinking of Atlantis.  The Obelisk was retrieved from the depths and hidden in the dungeon by Dagon's followers - it was brought up from the sea caves below.  The Obelisk communicates telepathically and can strike unbelievers who touch the stone blind.  Smashing the Obelisk destroys the magic prison and releases Dagon from an interdimensional space.  He's now an insane wizard with a god-complex and a bad attitude.  Great fun, right?

This is why jamming together a bunch of random results from giant mega tables is entertaining.  Dagon the mad wizard could make for an interesting campaign plot twist if the Obelisk is ever found and broken.  I doubt I would have put something like it in that area of the dungeon without being forced to reconcile some random inputs.

I hope this series has been helpful.  I'm at close to 500 rooms for Taenarum, the new dungeon, and a big part of why it's going so quickly is having tools that I like.  Whether the end result is good or not is a judgment best reserved for my players, but the two groups seem to be enjoying the campaign so far.  We got in a couple of family games over the holiday weekend, so I'll have some game reports before the end of the week.

Edit:  Based on requests in the comments, I've tried to put a sample Excel with the formulas out on a public DropBox - try to access it here:   VLookup Examples