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***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.

**Entries**column, which is simple text anyhow.

**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.

Very slick. One question:

ReplyDelete1. In your example did the VLOOKUP find 77 or 76?

VLOOKUP is using values from column A. It searches beginning in A2, and hits 76, which is larger than the searched-for 64. In that situation it returns the previous row. The 77 is a typo in the text (fixed).

ReplyDelete