Spreadsheet Help Needed!

[Bjarkmundur]

I thought about consolidating all my random tables into a spreadsheet.

Random Table	Column	Dice	Dice Result
Crit Table	Melee Weapon	1d100	56
This is a dropdown^	This is a dropdown^	This is fetched information based on the two drop- down menus chosen	This what we type in based on the physical dice roll

Then there's another cell with the effect.


We love random tables, and this would significantly reduce the time spent flipping through papers and finding the right table. The problem is that I don't know how to 'fetch' information based on conditions.

With this sheet we can simply roll the dice, and have the result appear in just a couple of clicks.

Can anyone help me code this?

EDIT: I started with my limited knowledge over here.
VLOOKUP seems to be helping, but it can only use one input. Anyone?

[Quizatzhaderac]

So answering your specific question about lookup with two inputs first.

In your lookup table, create the third column called "key", create the value by concatenating the values of the "table" and "column" columns. Do the vlookup by the concatenation of the values in the drop-downs.

More generally:

It seems to you trying to actually lookup tables and then do a second lookup on the specific table.

Do these tables all have the same set of columns? Because if not you'll need to lookup the column options based on the tables.

[Bjarkmundur]

Thank you kind stranger!

I'm trying to decide if I want to recompile all the tables I'm done writing up into separate tables in reverse order. I'll probably do both MATCH and VLOOKUP versions. Since I'm already here I might as well learn both methods.
You'll see this document in my signature once it's done. I'll turn off editing for now. Thanks again :D

[aimlessPolymath]

VLOOKUP does not have the ability to find "smallest number greater than equal to X" that MATCH does, so you'd need to write out your table fully, with one line for each possible die roll.

[Bjarkmundur]

VLOOKUP does not have the ability to find "smallest number greater than equal to X" that MATCH does, so you'd need to write out your table fully, with one line for each possible die roll.

Yeah the MATCH function is a much cleaner way to do it ^^

[playswithfire]

VLOOKUP does not have the ability to find "smallest number greater than equal to X" that MATCH does, so you'd need to write out your table fully, with one line for each possible die roll.

If you put the data in assorting order and set the fourth parameter of VLOOKUP(key, range, index, [sorted]) to true, it does the opposite, though, and finds the largest number less than or equal to X.

Your current version seems to be working well.