Random value from table with qualifier

[jasonkgreen]

I have a table of 1500 records. I want to populate a column with random states. My table with 1500 records has a region field already populated (Northeast, South, Midwest, West). I have another table with a list of the 50 states + DC each with a corresponding region (same as those listed above).

I found a formula that will return a random value from a list, but I need a qualifier to match the regions from each table and then return a random state where the regions match.

Suggestions?

=INDEX(data,RANDBETWEEN(1,ROWS(data)),1)

id	customer	revenue	support	region	industry	State
1​	1​	$3,787​	3​	West	1​	Populate random state here
2​	1​	$1,734​	3​	South	2​
3​	2​	$2,126​	3​	West	1​
4​	2​	$2,259​	3​	West	2​
5​	2​	$1,587​	4​	Northeast	1​
6​	1​	$0​	4​	West	3​
7​	2​	$1,838​	4​	South	1​
8​	1​	$1,847​	4​	West	1​
9​	1​	$1,714​	2​	Midwest	3​
10​	2​	$1,718​	4​	South	3​
11​	2​	$4,388​	1​	West	1​
12​	2​	$3,155​	1​	Northeast	2​
13​	2​	$3,834​	4​	West	1​
14​	2​	$2,291​	4​	Northeast	2​
15​	2​	$4,140​	2​	South	2​
16​	2​	$2,194​	2​	South	3​
17​	2​	$2,938​	2​	South	1​
18​	1​	$3,313​	4​	Midwest	2​
19​	2​	$3,327​	4​	Midwest	3​
20​	1​	$1,449​	2​	Midwest	1​

Using this table (partial table shown)

State	State Code	Region
Alaska	AK	West
Alabama	AL	South
Arkansas	AR	South
Arizona	AZ	West
California	CA	West
Colorado	CO	West
Connecticut	CT	Northeast
District of Columbia	DC	South
Delaware	DE	South
Florida	FL	South
Georgia	GA	South
Hawaii	HI	West
Iowa	IA	Midwest
Idaho	ID	West
Illinois	IL	Midwest
Indiana	IN	Midwest
Kansas	KS	Midwest
Kentucky	KY	South
Louisiana	LA	South

 

[DRSteele]

I think this will help.

MrExcel posts20.xlsx
	B	C	D	E	F	G	H
4	id	customer	revenue	support	region	industry	State
5	1	1	$3,787	3	West	1	ID
6	2	1	$1,734	3	South	2
7	3	2	$2,126	3	West	1
8	4	2	$2,259	3	West	2
9	5	2	$1,587	4	Northeast	1
10	6	1	$0	4	West	3
11	7	2	$1,838	4	South	1
12	8	1	$1,847	4	West	1
13	9	1	$1,714	2	Midwest	3
14	10	2	$1,718	4	South	3
15	11	2	$4,388	1	West	1
16	12	2	$3,155	1	Northeast	2
17	13	2	$3,834	4	West	1
18	14	2	$2,291	4	Northeast	2
19	15	2	$4,140	2	South	2
20	16	2	$2,194	2	South	3
21	17	2	$2,938	2	South	1
22	18	1	$3,313	4	Midwest	2
23	19	2	$3,327	4	Midwest	3
24	20	1	$1,449	2	Midwest	1
25
26
27	Using this table (partial table shown)
28	State	State Code	Region
29	Alaska	AK	West
30	Alabama	AL	South
31	Arkansas	AR	South
32	Arizona	AZ	West
33	California	CA	West
34	Colorado	CO	West
35	Connecticut	CT	Northeast
36	District of Columbia	DC	South
37	Delaware	DE	South
38	Florida	FL	South
39	Georgia	GA	South
40	Hawaii	HI	West
41	Iowa	IA	Midwest
42	Idaho	ID	West
43	Illinois	IL	Midwest
44	Indiana	IN	Midwest
45	Kansas	KS	Midwest
46	Kentucky	KY	South
47	Louisiana	LA	South
Sheet26

Cell Formulas
Range	Formula
H5	H5	=INDEX(FILTER(C29:C47,D29:D47=F5),RANDBETWEEN(1,COUNTIFS(D29:D47,F5)))