VLOOKUP with RAND function embed

Question

=VLOOKUP(RAND(),A3:B6,2)

The formula above is what I have been utilizing and it is not working on my excel when I generate in a workbook-gives me an #N/A, but the problem set solutions I am referencing for the answer never generates an #N/A. Why is my workbook generating an #N/A and another workbook that my professor develops is not calculating an #N/A with the exact same formula?

I looked at the data range it's reading off of and it's general numbers so it should read correctly.

Answer 1

With the formula =VLOOKUP(RAND(),A3:B6,2), if RAND() generates a value less than the value in A3, VLOOKUP will return #N/A.

So the value in A3 should be zero or negative in order to ensure that VLOOKUP always work.

Moreover, it should go without saying that the values in A3:A6 should be numeric and in ascending order.

However, other than the requirement above regarding A3, the values in A4:A6 can be anything.

In particular, they do not need to include 1. In fact, they do not even need to be less than 1 -- although arguably, the VLOOKUP expression seems pointless if they are greater than 1.

Answer 2

=rand() returns a number between 0 and 1 randomly.

Your lookup range, A3:B6, should have something like the table below, with first column in ascending order between 0 and 1 since =VLOOKUP(RAND(),A3:B6,2) - fourth parameter defaults to TRUE which expects lookup column in ascending order.

-	Quartile1
0.25	Quartile2
0.50	Quartile3
1.00	Quartile4