Using HLOOKUP and RANDBETWEEN to create random sets of things from two or more sets in OpenOffice.org Calc spreadsheets

Say you're trying to spice up your wardrobe and not just wear the same old outfits. You've got tops, pants, and shoes. You can put together a spreadsheet that randomly chooses one item from a list of tops, another random item from your list of pants, and maybe (depending on how adventurous you are) you get a new outfit you never would have thought of.

Or maybe you're a teacher or coach and trying to get your kids into different groups. You can randomly select one item from a list of captains, another from a list of cocaptains, and three or so from a list of people who just want to be on the team.

The nice thing about this is that whenever you close and re-open; or when you drag the formula down; or when you copy and paste; you get different choices because it's random.

You use HLOOKUP (or VLOOKUP) and you use RANDBETWEEN to specify the range of rows in which you want to pick something.

=HLOOKUP("Pants";D5:D25;RANDBETWEEN(2;20))

So you're looking under the column titled "Pants" in the range D5 to D25 and you're happy with being given back the value in the row that is anywhere from the 2nd item in that column to the 20th row in that column. (Note that the final parameter is relative, not absolute; if the range starts at 1 or 120, you still want to start at the 2nd item down, now the 1st or 120th. The 1st item is the column header.)

Note: There might well be a more efficient way to do this but it works. In these examples I have 2-3 separate ranges of one column each because I started out doing this with the columns in different sheets. But you could have just one range with multiple columns, so that you don't have to change the range every time.

Also: You need to use absolute references. So it would actually be:

=HLOOKUP("Pants";$D$5:$D$25;RANDBETWEEN(2;20))

And also remember that you can have the columns in different sheets, in which case it would be something like this:

=HLOOKUP("Pants";$clothing.$D$5:$D$25;RANDBETWEEN(2;20))

So let's do it. Here's the spreadsheet with all the data and formulas in it, if you'd like to look at a completed example.

First, you get your data. Put columns at the top. Let's say you want some random ideas for what clothes to wear, so put your tops in one column and pants in the other.

Then you copy over the column headings.

Then you need a formula. Here's what it would be without the absolute markers, just to make it simpler. I used the function wizard to make it a little clearer, too.

But you need $ in front of the cell references so add that. Here's the syntax.

=HLOOKUP("Tops";$I$1:$I$17;RANDBETWEEN(2;16))

Now either copy and paste, or drag, the column down to get values that are randomly chosen.

Here's the formula for the pants. Similar, except that you should change the cell references and the indicator.

=HLOOKUP("Pants";$J$1:$J$17;RANDBETWEEN(2;8))

Then drag or copy and paste down, and you get your different outfits.

Note: As mentioned before, in these examples I have 2-3 separate ranges of one column each because I started out doing this with the columns in different sheets. But you could have just one range with multiple columns, so that you don't have to change the range every time. You could set up the syntax for the tops like this:

=HLOOKUP("Tops";$I$1:$J$17;RANDBETWEEN(2;16))

And for the pants like this--note that the middle parameter, the range, stays the same.

=HLOOKUP("Pants";$I$1:$J$17;RANDBETWEEN(2;8))

However--if you have an unequal number of items in each column, then you will end up with blanks for some of your values for the shorter columns.

Another Example

You can do the same thing with captains, cocaptains, and team members.

 

Set up the data as above, then create headings. I've done them one above the other this time but it doesn't really matter. Note that the final parameters in the RANDBETWEEN are relative, not absolute.

  

And you get your results. Different every time you paste, or close and reopen.

Comments

[runescape money]

When you play games, you really thought about the Department of your equipment well enough, your account than others, your gold enough ... There are better! What you want to have here.come on..Let's go!

[NICK]

NO USE,MAKE IT VERY CLEAR,NO USE,MAKE IT VERY SIMPLE

[Dan]

This is a hilarious example. I'm curious, do you actually use a spreadsheet to help you figure out what to wear?

[jordan shoes]

air jordan shoes
michael jordan shoes
For further information about the Warhammer CD-key and Warhammer Timecard, Please keep an eye on air jordan shoes.

[wow power leveling]

http://www.gamegoldme.com/
http://www.wowgold-powerleveling.com/
http://www.wowgold-wow.com/
http://www.wowpowerleveling.me
http://www.watchrolexshop.com
http://www.wowgold-wow.com/wow-power-leveling
http://rs-runescapegold.com/
http://www.watchrolexshop.com/wow-power-leveling/
http://www.cheap-lotrogold.com/
http://www.globalsale.me/Aion-gold-083.aspx
http://www.cheap-gamegold.org
http://www.gamegoldvip.org
http://www.globalsale.me/

[michael jordan shoes]

Your article very interesting, I have introduced a lot of friends look at this article, the content of the articles there will be a lot of attractive people to appreciate, I have to thank you such an article.