Filter Data With Multiple Drop-Down Boxes and Multiple Criteria

Hello everyone,


I am making a spreadsheet for my mother and her team, but I am having difficulty writing a formula to filter data.

Any and all help is greatly appreciated!


Workbook is attached.

Note: I reduced the number of rows from 10,000 to 1,000 to reduce the file size.


The Data tab contains all the reference data.

The Settlements tab is mostly used for raw data entry.

The Settlements Filter tab is where I want to filter all the data in the Settlements tab, based on multiple criteria located in Drop-Down boxes.


The order of criteria are:

- Filter the settlements where Withdrawn = "No" in Column A (Settlements tab) AND where Applicant Name(s) is not blank in Column B (Settlements tab).

- If cell A3 (Settlements Filter tab) is blank, then display the text "Choose Year Type", otherwise if cell B3 (Settlements Filter tab) = "All Years" OR is blank, then display all settlements, otherwise if cell A3 (Settlements Filter tab) = "Fiscal" AND cell B3 (Settlements Filter tab) = "1999 - 2000" or "2000 - 2001" or "2001 - 2002" or ..... "2039 - 2040", then filter the settlements based on Column AD (Settlements Tab), otherwise if cell A3 (Settlements Filter tab) = "Calendar" AND cell B3 (Settlements Filter tab) = "2000" or "2001" or "2002" or ..... "2040", then filter the settlements based on Column AC (Settlements Tab), otherwise display the text "No Results".

- If cell C3 (Settlements Filter tab) = "All Months" OR is blank, then display all settlements, otherwise if cell C3 (Settlements Filter tab) = "January" or "February" or "March" or ..... "December", then filter the settlements based on Column AB (Settlements Tab), otherwise display the text "No Results".

- If cell D3 (Settlements Filter tab) = "All Brokers" OR is blank, then display all settlements, otherwise if cell D3 (Settlements Filter tab) = "Erin" or "Jackson" or "James" or ..... "Sue", then filter the settlements based on Column C (Settlements Tab), otherwise display the text "No Results".

- If cell E3 (Settlements Filter tab) = "All Lenders" OR is blank, then display all settlements, otherwise if cell E3 (Settlements Filter tab) = "Lender 1" or "Lender 2" or "Lender 3" or ..... "Lender 48", then filter the settlements based on Column F (Settlements Tab), otherwise display the text "No Results".

- If cell F3 (Settlements Filter tab) = "All Deal Types" OR is blank, then display all settlements, otherwise if cell F3 (Settlements Filter tab) = "Car/Equipment" or "Commercial" or "Residential", then filter the settlements based on Column G (Settlements Tab), otherwise display the text "No Results".

- If cell G3 (Settlements Filter tab) = "All Loan Types" OR is blank, then display all settlements, otherwise if cell G3 (Settlements Filter tab) = "Bridging" or "Construction (INV)" or "Construction (OO)" or ..... "Restructure", then filter the settlements based on Column H (Settlements Tab), otherwise display the text "No Results".

- If cell H3 (Settlements Filter tab) = "All Payment Status" OR is blank, then display all settlements, otherwise if cell H3 (Settlements Filter tab) = "Paid" or "Unpaid" or "Pending", then filter the settlements based on Column R (Settlements Tab), otherwise display the text "No Results".

- If cell I3 (Settlements Filter tab) = "All Loan Book Status" OR is blank, then display all settlements, otherwise if cell I3 (Settlements Filter tab) = "Yes" or "Overdue" or "Pending", then filter the settlements based on Column T (Settlements Tab), otherwise display the text "No Results".

- If cell J3 (Settlements Filter tab) = "All Referrers" OR is blank, then display all settlements, otherwise if cell J3 (Settlements Filter tab) = "Client Referral" or "Existing Client" or "GMB" or ..... "Social Media", then filter the settlements based on Column U (Settlements Tab), otherwise display the text "No Results".


I hope I explained that clearly.

The idea is that each criterion has to take into consideration all the other criteria.

My apologies if anything is unclear.


I have tried putting all of that into a formula using the FILTER function, but it always results in errors when I try to nest IF, AND (*) and OR (+) functions.

Perhaps I am going about this all wrong, but I figured it was time to consult the experts.


Thanks in advance!

Thank you JohnTopley; this is fantastic!

I really appreciate the enormous lengths you went to in order to help me.

Really, thank you so much. Have a great weekend!