Cascading dynamic drop down table

[GaryAR]

Hi everyone - I need to create a cascading dynamic drop down table that will work with duplicated data. The full table is 12 columns x 7800 rows. I attach a test sheet to show the basic issue.. I would prefer a formula based solution if possible rather than VBA. Any thoughts greatly appreciated!!

Test Sheet.xlsx
	B	C	D	E	F
4	Scope	Level 1	Level 2	Level3	Factor
5
6
7
8	Scope	Level_1	Level_2	Level_3	Factor
9	Scope 1	Fuels	Gaseous fuels	CNG	0.18387
10	Scope 1	Fuels	Gaseous fuels	PNG	0.20374
11	Scope 1	Energy	Gas	PIH	0.44327
12	Scope 1	Energy	Diesel	Red	2533
13	Scope 2	Food	Bread	Brown	0.18455
14	Scope 2	Food	Butter	Salted	0.20449
15	Scope 2	Food	Milk	Low fat	1.15041
16	Scope 2	Material	Wood	Soft	2542.41
17	Scope 2	Material	Wood	Hard	0.21448
18	Scope 2	Material	Paper	White	0.2303
19	Scope 3	Water	Hot	Low e	1.55537
20	Scope 3	Water	Hot	High e	2938.81
21	Scope 3	Water	Cold	Low e	0.18387
22	Scope 3	Soils	Red	Firm	0.20374
23	Scope 3	Soils	Brown	Soft	2.02266
24	Scope 3	Soils	Blue	Med	2533
Calculator

Cell Formulas
Range	Formula
C6	C6	=IFERROR(INDEX(B9:C24,MATCH(0,INDEX(COUNTIF($B$4:B4,B9:B24),),0)),"")

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Calculator!$B$4:$F$6	C6

Cells with Data Validation
Cell	Allow	Criteria
D6	List	=OFFSET(#REF!,1,MATCH($B6,#REF!,0)-1,COUNTA(OFFSET(#REF!,1,MATCH($B6,#REF!,0)-1,20)),1)
B5	List	=$B$9:$B$24
C5	List	=$C$9:$C$24