You are here: Home / Archives for Random Lookup

Random Lookup Revisited

May 25, 2008 By

In a previous post I explained how to perform random lookup based on the VLOOKUP function. The formula was:

=VLOOKUP(INT(RAND()*10+3),P12:Q23,2,FALSE)

I explained how I used the RAND() function to generate values between 1 and 12 for the first parameter of the function (red color). However there is another direct function which generates random numbers within a given range called RANDBETWEEN. To get this function you must install an add-in called the analysis tool pack.

When this function is available then we can replace the previous formula with the new one:

=VLOOKUP(RANDBETWEEN(1,12),P12:Q23,2,FALSE)

This will generate random numbers between 1 and 12.

All other steps will remain as explained in the previous post.

How to install the analysis tool pack

1. In the Tools menu click Add-ins. The Add-in window will open.
2. Click on the checkbox next to the Analysis Tool Pack.
3. Click OK then follow the instructions on the screen (you may be asked to insert MS Excel setup CD).

Applies to: Excel 2003

Filed Under: Data, Functions Tagged With: , ,

Random lookup based on VLOOKUP function

May 21, 2008 By

A few months ago I designed a monthly calendar template in Excel. One of my requirements was to pick a proverb randomly from a list and show it in the calendar. This is how I managed to do it:

I first created the list of proverbs as shown below.

xproverbs.gif

This is the calendar template

Calendar

To pick the proverb randomly I placed the VLOOKUP in the cell where I want the proverb to appear.

=VLOOKUP(INT(RAND()*10+3),P12:Q23,2,FALSE)

Now let us dissect the code (Please refer to the post on How to use the VLOOKUP Function for details):

1. =VLOOKUP(INT(RAND()*10+3),P12:Q23,2,FALSE)

This is our lookup value which generated randomly using the RAND() function, and because RAND() returns a fraction I multiplied by 10 to get a whole number. In my case I have 12 items in my table and this is why I added 3 hear to make sure that I will cover all of the table. The INT() function is used to get rid of the fraction to the right of the decimal.

2. =VLOOKUP(INT(RAND()*10+3),P12:Q23,2,FALSE)

This is where the table array is (the list of proverbs).

3. =VLOOKUP(INT(RAND()*10+3),P12:Q23,2,FALSE)

The 2 is the column index number. I.e. we are picking our proverb from the second column in the table array.

4. =VLOOKUP(INT(RAND()*10+3),P12:Q23,2,FALSE)

This is the range lookup parameter. “False” means we are looking for an exact match of the lookup value.

Pleas check this post for a different approach to random lookup:

Applies to: Excel 2003

Filed Under: Data, Functions Tagged With: , ,