How to Randomly Look Up and Select from Reference Table?

[ddub25]

I am trying to generate random outputs into OUTPUT TABLE 1 and OUTPUT TABLE 2 of the ITEM column.

There will be options to select from drop downs in the OPTIONS TO SELECT table of TYPE, LEVEL and LOCATION.

Output Tables 1 and 2 then get automatically populated with these chosen options in their respective columns.

The ITEM column of these tables should then randomly select a row from the Reference Table where it finds a match for criteria of ‘Cat.’, ‘Type’, ‘Lev.’, and ‘Loc.’

Of course, more than one match maybe be found and so one option needs to be randomly selected. There are situations where no matches will be found and so that result needs to be left blank in the ITEMS column of each table. There will also be situations where only one option will be found and so, that option would be selected by default.

Rows that have an ‘n’ in the ‘Act.’ Column of the Reference Table should be ignored.

It would be greatly appreciated if someone can point me in the right direction to find a solution to this problem. Thanks in advance, Dan.

Options To Select
Type	STR
Level	2
Location	F

Output Table 1
Cat.	Type	Lev.	Loc.	Item
CR	STR	2	F	12
CPD	STR	2	F	2
PSU	STR	2	F	30
PLU	STR	2	F	21	Only option available and so it chooses it

Output Table 2
Cat.	Type	Lev.	Loc.	Item
CR	MOB	2	H	16
CPD	MOB	2	H	6	Only option available and so it chooses it
PSU	MOB	2	H	35
PLU	MOB	2	H	24	Only option available as other option is NON active ('n' in 'Act.' Column), and so it chooses it

Reference Table				Loc.
Area	Cat.	Type	Lev.	H	F	Act.	Item
CPD	CPD	STR	2	y			1
CPD	CPD	STR	2	y	y		2
CPD	CPD	FLX	1				3
CPD	CPD	FLX	2	y		n	4
CPD	CPD	MOB	1		y		5
CPD	CPD	MOB	2	y	y		6
CPD	CPD	SK	2	y			7
CPD	CPD	SK	2			n	8
CPD	CPD	STR	2		y		9
CR	CR	STR	1	y	y		10
CR	CR	STR	2		y		11
CR	CR	STR	2	y	y		12
CR	CR	FLX	2				13
CR	CR	FLX	1	y	y		14
CR	CR	MOB	2	y			15
CR	CR	MOB	2	y			16
CR	CR	MOB	1	y			17
CR	CR	SK	2	y	y		18
CR	CR	SK	2	y	y	n	19
UB	PLU	STR	1		y		20
UB	PLU	STR	2	y	y		21
UB	PLU	FLX	2				22
UB	PLU	FLX	1		y		23
UB	PLU	MOB	2	y	y		24
UB	PLU	MOB	2	y		n	25
UB	PLU	SK	1		y		26
UB	PLU	SK	2	y			27
UB	PSU	STR	1		y		28
UB	PSU	STR	2	y	y		29
UB	PSU	STR	2		y		30
UB	PSU	FLX	2	y		n	31
UB	PSU	FLX	2	y	y		32
UB	PSU	MOB	1	y			33
UB	PSU	MOB	2	y	y		34
UB	PSU	MOB	2	y			35
UB	PSU	SK	2		y		36
UB	PSU	SK	2	y	y	n	37

 

[ddub25]

I have been advised to put up my post in the form of a Mini Sheet and so I am reposting the data in that form.

Book2.xlsx
	A	B	C	D	E	F	G	H	I		K	L	M	N	O	P	Q	R	S	T
1
2												Options To Select			Output Table 1
3												Type	STR		Cat.	Type	Lev.	Loc.	Item
4												Level	2		CR	STR	2	F	12
5												Location	F		CPD	STR	2	F	2
6															PSU	STR	2	F	30
7															PLU	STR	2	F	21	Only option available and so it chooses it
8
9															Output Table 2
10															Cat.	Type	Lev.	Loc.	Item
11															CR	MOB	2	H	16
12															CPD	MOB	2	H	6	Only option available and so it chooses it
13															PSU	MOB	2	H	35
14															PLU	MOB	2	H	24	Only option available as other option is NON active ('n' in 'Act.' Column), and so it chooses it
15		Reference Table				Loc.
16		Area	Cat.	Type	Lev.	H	F	Act.	Item
17		CPD	CPD	STR	2	y			1
18		CPD	CPD	STR	2	y	y		2
19		CPD	CPD	FLX	1				3
20		CPD	CPD	FLX	2	y		n	4
21		CPD	CPD	MOB	1		y		5
22		CPD	CPD	MOB	2	y	y		6
23		CPD	CPD	SK	2	y			7
24		CPD	CPD	SK	2			n	8
25		CPD	CPD	STR	2		y		9
26		CR	CR	STR	1	y	y		10
27		CR	CR	STR	2		y		11
28		CR	CR	STR	2	y	y		12
29		CR	CR	FLX	2				13
30		CR	CR	FLX	1	y	y		14
31		CR	CR	MOB	2	y			15
32		CR	CR	MOB	2	y			16
33		CR	CR	MOB	1	y			17
34		CR	CR	SK	2	y	y		18
35		CR	CR	SK	2	y	y	n	19
36		UB	PLU	STR	1		y		20
37		UB	PLU	STR	2	y	y		21
38		UB	PLU	FLX	2				22
39		UB	PLU	FLX	1		y		23
40		UB	PLU	MOB	2	y	y		24
41		UB	PLU	MOB	2	y		n	25
42		UB	PLU	SK	1		y		26
43		UB	PLU	SK	2	y			27
44		UB	PSU	STR	1		y		28
45		UB	PSU	STR	2	y	y		29
46		UB	PSU	STR	2		y		30
47		UB	PSU	FLX	2	y		n	31
48		UB	PSU	FLX	2	y	y		32
49		UB	PSU	MOB	1	y			33
50		UB	PSU	MOB	2	y	y		34
51		UB	PSU	MOB	2	y			35
52		UB	PSU	SK	2		y		36
53		UB	PSU	SK	2	y	y	n	37
Excel Post

Cell Formulas
Range	Formula
P4:P7	P4	=$M$3
Q11:Q14,Q4:Q7	Q4	=$M$4
R4:R7	R4	=$M$5