Random Tables II - More Excel

A few days back I posted an ultra-simple example of a random table in Excel. Since I'm making a few tables for another project, I thought I'd do a slightly more complex (and useful) example, and explain how it works. Here's the basic table, which uses a percentile dice roll and has variable ranges for each table entry.

The user-created data entries are the Range and Entries columns of the table, and the first entry in the Min column. Everything else is a formula. Here's what various entries mean:

• Min and Max are the die roll results that match the table result from the Entries column. For example, a roll result of 51 to 75, inclusive, matches the Tigers result in the Entries column. The values of all but the first cell of Min and Max are calculated from the Range column.
• Range is the number of die results that are used by a particular line of the table. If you add up the values of the Range column, you get 100, the number of possible results on a d100.
• The confusingly labeled Min and Max cells below the table contain formulas that grab the first value from the Min column and the last value from the Max column.
• The Roll cell contains our friend RANDBETWEEN, and uses the Min and Max cell entries to determine the range.
• Result uses the contents of Roll to peform a table lookup using a new function VLOOKUP. Where INDEX looks at row and column numbers, VLOOKUP (and the similar HLOOKUP) look at cell values to select results.

That probably sounds confusing, so here's an image showing all the formulas in this table. Because of space limits I chopped off the Entries column, which is simple text anyhow.

Let's first look at the Min and Max columns. As you can see, each Max value is calculated as the corresponding Min plus the Range minus one (to account for the Min value itself). Each Min value after the first is equal to the previous line's Max plus one.

Next check out the Min and Max cells down in the formulas section. Note that they're straight cell references to the first and last line of the matching columns. Below those you can see the RANDBETWEEN function, which produces a die roll for us. You can also see that I've applied names to the Min and Max cells for ease of reading.

Last, we come to the actual Result. The VLOOKUP function has three (four actually, but we'll get to that), parameters. The first is what we're looking up, in this case the contents of B11, Roll. The second is the array we're examining, which is defined as A2:D7, the actual data of our Min, Max, Range, and Entries cells. The third parameter is a column number, 4, which tells VLOOKUP which column to return the value from. But what does this function actually do? It scans down the first column of the given array until it finds a value that matches (more on that in a moment) the first parameter. When it finds a match, it returns the value from the indicated column, the Entries column in our sample.

But wait! In the first image above, the roll was 64, and that doesn't match 51! That's correct. VLOOKUP does an approximate match. If it finds a value in column one larger than the desired search result, it returns the previous row's value. In the case of 64, it hit 76, which is larger, and returned column 4 of the previous row, which is exactly what we want. There are some gotchas inherent in this:

• If your first row entry is larger than the search parameter, VLOOKUP returns N/A.
• Your lookup values must be sorted, smallest to largest, and all blanks/spaces need to be removed from the cell entries or Bad Things (TM) can happen. Also note that numbers in cells defined as text don't work as you would expect! Garbage in, garbage out.
• You can alter the approximate vs. exact match behavior using the optional fourth parameter to VLOOKUP. The default is approximate matches. Add a fourth parameter of FALSE to use exact matching. This will not work at all with these examples.
• If you prefer horizontal tables you can use VLOOKUP's buddy, HLOOKUP. It functions identically except it uses the first row of the table as a lookup and returns the given row as a result.

If you want to see this in action (minus cell names), you can check out Sheet 2 of this Google Spreadsheet.