Forum Discussion
Solved
How to Index Across Multiple Columns in a Different Worksheet, then Get Random Value from Column
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...
I'd insert a row with the abbreviations on the Cities sheet:
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)))))
I'd insert a row with the abbreviations on the Cities sheet:
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)))))
Worked perfectly, thank you Hans!!!