vlookup based on random result returns incorrect result

I am trying to create an excel spreadsheet that will generate a city from a
random province.

I create a list of three provinces

Table 1
0 Alberta
..34 Saskatchewan
..67 Manitoba

I then created the following list

Table 2
Alberta Calgary
Saskatchewan Regina
Manitoba Winnipeg

I picked a random province by picking a random number and using that number
to reference table 1

A5 = vlookup(rand(),reference to list 1,2)

I then tried to pick the capital of the province with another vlookup
function, referencing A5 to get the province and matching it with the
matching city in Table 2

A6 =vlookup(A5,reference to list
2,2)

The result should have been that if A5 returned Manitoba, A6 should find the
word Manitoba in the second list and return Winnipeg in A6.

This regularily returns results such as Alberta,Regina which is not a viable
pair. I don't know what I am doing wrong.

Any suggestions would be helpful.

Thank You

Hi

You need the FALSE or 0 4th argument in your Vlookup because the list isn't
sorted alphabetically.
=vlookup(A5,reference to list2 ,2,0)


Regards

Roger Govier


rickat wrote:
> I am trying to create an excel spreadsheet that will generate a city from a
> random province.
>
> I create a list of three provinces
>
> Table 1
> 0 Alberta
> .34 Saskatchewan
> .67 Manitoba
>
> I then created the following list
>
> Table 2
> Alberta Calgary
> Saskatchewan Regina
> Manitoba Winnipeg
>
> I picked a random province by picking a random number and using that number
> to reference table 1
>
> A5 = vlookup(rand(),reference to list 1,2)
>
> I then tried to pick the capital of the province with another vlookup
> function, referencing A5 to get the province and matching it with the
> matching city in Table 2
>
> A6 =vlookup(A5,reference to list
> 2,2)
>
> The result should have been that if A5 returned Manitoba, A6 should find the
> word Manitoba in the second list and return Winnipeg in A6.
>
> This regularily returns results such as Alberta,Regina which is not a viable
> pair. I don't know what I am doing wrong.
>
> Any suggestions would be helpful.
>
> Thank You