Help Needed - Cascading Lists With Data Validation - Unsorted List Multiple Criteria

Hi There,

I have a products database which has 3 categories and then the product name on a sheet called PRODUCTS
Column A, Column B, Column C, Column D
Catergory 1, Category 2, Catergory 3, Goods Description

Construction, Walls, Interior, - Gypsum Interior Wall
Construction, Walls, Interior, - Ply Interior Wall
Furniture,Bathroom,Cabinet - Mirror
Furniture,Kitchen,Cabinet, Case Unit
Appliances,Kitchen,Ovens - 4 Burner Hob

There are 1000's of products and only the FIRST Column is sorted alphabetically. Other columns (sub categegories) can container the same value or different

I have a Form for staff to complete which has 4 drop down boxes so they can first choose the Main Category, then the 2nd then 3rd and be left with the products which fit those 3. They then do this multiple times on the main sheet to make a Bill Of Quantities.

I have tried making the first 3 list boxes with Dynamic Ranges and putting the Categories on a separate sheet called formulas. I put The Category 1 in 1 column, then category 2 in another column with all the different variables available, and then a 3rd column with all the different Category 2 and Category 3 combinations. My thinking was to refernce these and then use them as a search value to return all cells from the product sheet which matched. The first 3 list boxes work but i cannot generate a 4th listbox with all the matching products.

Formulas Sheet
Column H Column J & K Column M&N
Catergory1 Catergory2 Type category3 type
ACCESSORIES ACCESSORIES AWNING ACC SPLIT
APPLIANCES ACCESSORIES CUSTOM ACC OTHER
CONSTRUCTION ACCESSORIES DECKING AWNING STEEL
DOORS ACCESSORIES POOL AWNING CLOTH
ELECTRICAL ACCESSORIES RAILINGS AWNING OTHER
EXTERIOR ACCESSORIES RECYCLING BATHROOM EXTRACTOR
FURNITURE ACCESSORIES ROOF TERRACE BATHROOM HEATER
PLUMBING ACCESSORIES SAFETY BATHROOM CABINET
WINDOWS ACCESSORIES SHUTTERS BATHROOM OTHER
ACCESSORIES OTHER BEDROOM CABINET
APPLIANCES ACC BEDROOM CUSTOM
APPLIANCES KITCHEN BEDROOM OTHER
APPLIANCES OTHER CONTAINER PREPARATION
CONSTRUCTION CONTAINER CONTAINER CONTAINER
CONSTRUCTION FASCIA CONTAINER OTHER
CONSTRUCTION FLOORING CUSTOM STEEL

I done the above using OFFSET as follows

=OFFSET(Formulas!$M$1,MATCH(C17,Formulas!$M:$M,0)-1,1,COUNTIF(Formulas!$M:$M,C17),1)


Can anyone help me? I have been trying for ages using INDEX, MATCH, and searched everywhere but cant seem to find the right answers. All other columns are not sorted alphabetically.

Any help would be extremely grateful. Will try to upload screen shots or file also

Thanks

Sorry the sample data is attached here.

Thanks

@FlameRetired

THANK YOU! You are a star.... It works perfectly so far. Will go over the sheet and let you know if I have any problems. I spent 2 days on it trying to figure it out. Makes sense now you have explained things.