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*