Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
rickat
 
Posts: n/a
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roger Govier
 
Posts: n/a
Default vlookup based on random result returns incorrect result

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
"1235" appears as "One thousand two hundred thirty five" H. Kan Excel Discussion (Misc queries) 11 December 8th 06 08:56 PM
Amount or Numbers in Words ron New Users to Excel 6 December 24th 04 08:32 PM
Convert Numeric into Text Monty Excel Worksheet Functions 0 December 18th 04 10:25 PM
Spellnumber Norman Jones Excel Worksheet Functions 6 December 13th 04 08:21 AM
Determning the ROW of a vlookup result Markshnier Excel Worksheet Functions 2 November 15th 04 02:57 PM


All times are GMT +1. The time now is 06:42 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"