SOLVED

How to Index Across Multiple Columns in a Different Worksheet, then Get Random Value from Column

Occasional Contributor

Have an excel spreadsheet with over 45,000 rows, two worksheets, first one is called Users, second is called Cities. Generating fake data for our software, the addresses are fake, however, the cities for each province need to be real and have been generated from real data

Simplified Example: column City is blank in the Users worksheet, a random city would come from the Cities worksheet
Users worksheet:
Name      Address                     City         Province
Anne      123 Buckey Street                      British Columbia
Joe         346 Argyle Road                        British Columbia
Bill          675 Rollie Avenue                     Alberta

Tom        348 Gasper Road                      Saskatchewan

Cities worksheet:
Each Province has a Named Range for all values as the # of rows is different: BC, AB, SK
Across the top created a Named Range of all the provinces: PROVINCES from A1:M1
Another Named range for all the values in Cities is called CANADA 

 

British Columbia      Alberta            Saskatchewan      Manitoba
100 Mile House       Alpen               Abbott                 Arnes
Kelowna                   Bearspaw         Canora                Digges
Surrey                      Deer Hill          Echo Bay              Hilton
Tumbler Ridge         Fort Kent         La Ronge              Paulson  
Vancouver                Muriel Lake     Slave Falls             The Pas
Victoria                     Wastina           Yonker                  Wood Bay   

I want to match the Province in each row in the Users Worksheet to the top row in the Cities worksheet, once the province is matched up, pull a random value in that matching column

Example: In the row Joe, a random city would be generated for the column City from the BC named range in the Cities Worksheet

I tried Index(CANADA(MATCH(C2,PROVINCES,1),RAND(BC,AB,SK))

2 Replies
best response confirmed by ecomkid (Occasional Contributor)
Solution

@ecomkid 

I'd insert a row with the abbreviations on the Cities sheet:

S1928.png

You can then use this formula in C2 on the Users sheet:

=INDEX(INDIRECT(HLOOKUP(D2,Cities!$A$1:$M$2,2,FALSE)),RANDBETWEEN(1,COUNTA(INDIRECT(HLOOKUP(D2,Cities!$A$1:$M$2,2,FALSE)))))

S1929.png

 

Worked perfectly, thank you Hans!!!