Random number & Vlookup

[BladeFisher]

I am wanting to generate a random number then in the same function take the resultant number compare to a list and it is the result of the list that is entered into the cell.

E.g. Rand ()*100 say answer is 37 and in the lookup 37 equates to Oxford. Lookup table = 2 column, 100 entries, different answers for each value which is a text string.

Sounds simple but getting tied up in knots

 

[Rick Rothstein]

Assuming your lookup table is in A1:B100 and your random number is in C1, then I think this formula does what you want...

=VLOOKUP(C1,A2:B100,2)

 

[T. Valko]

I am wanting to generate a random number then in the same function take the resultant number compare to a list and it is the result of the list that is entered into the cell.

E.g. Rand ()*100 say answer is 37 and in the lookup 37 equates to Oxford. Lookup table = 2 column, 100 entries, different answers for each value which is a text string.

Sounds simple but getting tied up in knots

For that to happen RAND() would have to return EXACTLY 0.37.

The chances of that happening are extemely slim.

Maybe the problem you're having is that your lookup table consists of whole numbers and RAND()*100 is returning values like 37.01230254821024588.

If you want a random number from 1 to 100 then use the RANDBETWEEN() function:

=VLOOKUP(RANDBETWEEN(1,100),...

Note that the RANDBETWEEN function requires the Analysis ToolPak add-in be installed if you're using a version of Excel prior to Excel 2007. If you
enter the formula and get a #NAME? error look in Excel help for the RANDBETWEEN function. It'll tell you how to fix the problem.

Or, if you're using Excel 2003 or earlier and you don't/can't have the ATP installed...

INT(RAND()*100+1)

=VLOOKUP(INT(RAND()*100+1),...

 

[shg]

For that to happen RAND() would have to return EXACTLY 0.37.

I think anything between [0.37 ... 0.38)

 

[T. Valko]

I think anything between [0.37 ... 0.38)

How can 0.371235689 * 100 return EXACTLY 37?

 

[shg]

It wouldn't, but it need not be for a range lookup, which is what the OP's formula is, Tony.

 

[T. Valko]

It wouldn't, but it need not be for a range lookup, which is what the OP's formula is, Tony.

We don't know what the OPs formula looks like. They haven't posted one.

All we know is that the OP says RAND()*100 might return 37 which is very unlikely.

We need further clarification from the OP.

 

[BladeFisher]

For that to happen RAND() would have to return EXACTLY 0.37.

The chances of that happening are extemely slim.

Maybe the problem you're having is that your lookup table consists of whole numbers and RAND()*100 is returning values like 37.01230254821024588.

If you want a random number from 1 to 100 then use the RANDBETWEEN() function:

=VLOOKUP(RANDBETWEEN(1,100),...

Note that the RANDBETWEEN function requires the Analysis ToolPak add-in be installed if you're using a version of Excel prior to Excel 2007. If you
enter the formula and get a #NAME? error look in Excel help for the RANDBETWEEN function. It'll tell you how to fix the problem.

Or, if you're using Excel 2003 or earlier and you don't/can't have the ATP installed...

INT(RAND()*100+1)

=VLOOKUP(INT(RAND()*100+1),...

Thanks for that. Yes sorted the query. Am using an old version of Excel.

Regards

Richard

 

[T. Valko]

Thanks for that. Yes sorted the query. Am using an old version of Excel.

Regards

Richard

Good deal. Thanks for the feedback!